Oracle Study Notes: exp data export tool usage

1. Detailed description of export tool parameters

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