Oracle Study Notes: exp data export tool usage

Source: Internet
Author: User

1. Detailed description of export tool parameters

  1. [Oracle @ linux exp] $ exp help = y
  2. Export: Release 10.2.0.4.0-ProductionOnWednesday, September 16, October 5 15:08:57, 2011
  3. Copyright (c) 1982,200 7, Oracle.AllRights reserved.
  4. Enter the EXP command and your username/password to export
  5. You are prompted to enter the following parameters:
  6. Example: exp scott/TIGER
  7. Alternatively, you can control the export by entering the EXP command with various parameters
  8. . To specify parameters, you can use the Keyword:
  9. Format: exp keyword = value or KEYWORD = (value1, value2,..., valueN)
  10. Example: exp scott/tiger grants = y tables = (EMP, DEPT, MGR)
  11. Or TABLES = (T1: P1, T1: P2). If T1 is a partitioned table
  12. USERID must be the first parameter in the command line.
  13. Keyword description (default) keyword description (default)
  14. --------------------------------------------------------------------------
  15. USERID username/passwordFULLExport the entire file (N)
  16. BUFFER data BUFFER size OWNER User Name List
  17. FILE output FILE (EXPDAT. DMP) TABLES Table Name List
  18. Length of the IO record that COMPRESS imports to a partition (Y) RECORDLENGTH
  19. GRANTS export permission (Y) INCTYPE incremental export type
  20. INDEXES export index (Y) RECORD trace incremental Export (Y)
  21. DIRECT path (N) TRIGGERS export trigger (Y)
  22. LOG File output by LOG ScreenSTATISTICSAnalysis object (ESTIMATE)
  23. ROWSExport data row (Y) PARFILE parameter file name
  24. CONSISTENT cross tabulation consistency (N) CONSTRAINTS exported by CONSTRAINTS (Y)
  25. OBJECT_CONSISTENT is set to read-only transaction processing only during object Export (N)
  26. The progress of FEEDBACK is displayed on every x rows (0)
  27. FILESIZE: maximum size of each dump.
  28. FLASHBACK_SCN is used to set the session snapshot back to the previous state SCN
  29. FLASHBACK_TIME is used to obtain the SCN time closest to the specified time.
  30. QUERY is used to export the subset of a tableSelectClause
  31. When the RESUMABLE encounters a space-related error, it suspends (N)
  32. RESUMABLE_NAME is a text string used to identify a recoverable statement.
  33. The waiting time of RESUMABLE_TIMEOUT RESUMABLE.
  34. TTS_FULL_CHECK performs a full or partial correlation check on TTS.
  35. VOLSIZE number of bytes written to each Tape Volume
  36. Table space list of TABLESPACES to Be Exported
  37. TRANSPORT_TABLESPACE export the table space metadata that can be transferred (N)
  38. TEMPLATE: name of the TEMPLATE to be exported in iAS mode.
  39. The export is successfully terminated without a warning.

2. Common export commands

Full mode Export (entire database)

  1. Exp system/oracle @ orclFull= Y file = demo1.dmp log = demo1.log

Export in user mode (export all objects under user ing)

  1. Exp system/oracle owner = ing file = demo2.dmp log = demo2.log

Export in Table mode (export the dept table under user ing)

  1. Exp system/oracle tables = ing. dept file = demo3.dmp log = demo3.log

Export with the where condition (export the first two rows of the dept table under the user ing)

  1. Exp system/oracle tables = ing. dept query = \"Where rownum \ <3 \"File = demo4.dmp log = demo4.log

Fuzzy Export (export tables starting with de under user ing)

  1. Exp system/oracle @ orcl tables = ing.de % file = demo5.dmp log = demo5.log

3. view the Oracle character set through the dmp File
First, open the dmp file through the editing software such as UE or execute the following in Linux and then check the second and third bytes. If the dmp file is large, for example, 2 GB or above (this is also the most common case), you can use the following command (on a linux host) to open it slowly or completely without using a text editor ):

  1. [Oracle @ linux exp] $ cat demo5.dmp | od-x | head-1 | awk'{Print $2 $3 }'| Cut-c 3-6
  2. 0345
  1. SQL>SelectNls_charset_name (to_number ('123','Xxxx'))FromDual;
  2. NLS_CHARSET_NAME (TO_NUMBER ('123','Xxxx'
  3. ----------------------------------------
  4. ZHS16GBK

4. Export Permission Problems

For normal users to export and import data, you must grant permissions.

  1. SQL>GrantExp_full_database, imp_full_databaseToIng;
  2. Authorization successful.

5. Character Set Problems

The character set of the client is the same as that of the database during export.

  1. [Oracle @ linux exp] $ echo $ NLS_LANG
  2. SIMPLIFIED CHINESE_CHINA.ZHS16GBK

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.