Oracle Study Notes: Use the expdp/impdp Data Pump to export/import data

Data Pump is a new function launched by Oracle 10g. The difference between expdp/impdp AND exp/imp is that exp/imp is a client tool and can be used either on the client or on the server. Expdp/impdp is a server tool and can only be used on the server.

Expdp parameter description

1. [Oracle @ linux exp] $ expdp help = y
3. Export: Release 10.2.0.4.0-ProductionOnWednesday, October, 2011 17:30:17
5. Copyright (c) 2003,200 7, Oracle.AllRights reserved.
8. The data pump export utility provides a method for transferring data between Oracle databases.
9. Data Object mechanism. The utility can be called using the following command:
11. Example: expdp scott/tiger DIRECTORY = dmpdir DUMPFILE = scott. dmp
13. You can control the export running mode. The specific method is as follows:'Expdp'Enter
14. Parameters. To specify parameters, use the Keyword:
16. Format: expdp KEYWORD = value or KEYWORD = (value1, value2,..., valueN)
17. Example: expdp scott/tiger DUMPFILE = scott. dmp DIRECTORY = dmpdir SCHEMAS = scott
18. Or TABLES = (T1: P1, T1: P2). If T1 is a partitioned table
20. USERID must be the first parameter in the command line.
22. Keyword description (default)
23. ------------------------------------------------------------------------------
24. ATTACH connects to an existing job, for example, ATTACH [= job name].
25. COMPRESSION reduces the size of valid dump files
26. Keyword values: (METADATA_ONLY) and NONE.
27. CONTENT specifies the data to be detached. The valid keyword is:
28. (ALL), DATA_ONLY and METADATA_ONLY.
29. DIRECTORY is the DIRECTORY object used by dump files and log files.
30. The list of DUMPFILE target dump files (expdat. dmp,
31. For example, DUMPFILE = scott1.dmp, scott2.dmp, dmpdir: scott3.dmp.
32. ENCRYPTION_PASSWORD is the key word used to create an encryption column.
33. ESTIMATE calculates the estimated job value. The valid keyword is:
34. (BLOCKS) andSTATISTICS.
35. ESTIMATE_ONLY calculates the estimated job value without executing the export operation.
36. EXCLUDE to EXCLUDE specific object types, such as EXCLUDE =TABLE: EMP.
37. FILESIZE specifies the size of each dump in bytes.
38. FLASHBACK_SCN is used to set the session snapshot back to the SCN in the previous state.
39. FLASHBACK_TIME is used to obtain the SCN time closest to the specified time.
40. FULLExport the entire database (N ).
41. HELP displays the HELP message (N ).
42. INCLUDE includes specific object types, such as INCLUDE = TABLE_DATA.
43. The name of the export job to be created in JOB_NAME.
44. LOGFILE log File Name (export. log ).
45. The name of the remote database to which NETWORK_LINK is linked.
46. NOLOGFILE does not write log files (N ).
47. PARALLEL changes the number of active workers of the current job.
48. PARFILE specifies the parameter file.
49. QUERY is the predicate clause used to export a subset of a table.
50. The percentage of data to be exported in SAMPLE;
51. List of schemes to be exported by SCHEMAS (logon scheme ).
52. When the default value (0) shows the new STATUS when available,
53. The frequency (in seconds) job status to be monitored.
54. TABLES identifies the list of TABLES to be exported-There is only one solution.
55. TABLESPACES identifies the list of TABLESPACES to be exported.
56. TRANSPORT_FULL_CHECK verifies the storage segments of all tables (N ).
57. TRANSPORT_TABLESPACES: List of tablespaces from which metadata is to be detached.
58. The VERSION of the object to be exported. The valid keyword is:
59. (COMPATIBLE), LATEST or any valid database version.
61. The following commands are valid in interactive mode.
62. Note: abbreviations are allowed.
64. Command description
65. ------------------------------------------------------------------------------
66. ADD_FILE adds a dump file to the dump set.
67. CONTINUE_CLIENT returns to record mode. If the job is idle, the job is restarted.
68. EXIT_CLIENT exits the Client Session and keeps the job running.
69. The default file size (in bytes) of the subsequent ADD_FILE command ).
70. HELP summarizes interactive commands.
71. KILL_JOB separates and deletes jobs.
72. PARALLEL changes the number of active workers of the current job.
73. PARALLEL = <number of workers>.
74. START_JOB start/restore the current job.
75. When the default value (0) shows the new STATUS when available,
76. The frequency (in seconds) job status to be monitored.
77. STATUS [= interval]
78. STOP_JOB closes the executed job in sequence and exits the client.
79. STOP_JOB = IMMEDIATE will be closed immediately
80. Data pump operation.

The Data Pump must depend on the directory.

1. SQL> showUser
2. USERIs"SYS"
3. SQL>CreateDirectory expAs '/U01/exp';
5. The directory has been created.

Full mode Export

1. Expdp system/oracle @ orcl directory = expFull= Y

User Mode Export

1. Expdp system/oracle @ orcl directory = exp schemas = ing dumpfile = demo1.dmp logfile = demp1.log

Tablespace mode Export

1. Expdp system/oracle @ orcl directory = exp tablespaces = test01

Table mode Export

1. Expdp system/oracle @ orcl directory = exp tables = ing. dept

1. [Oracle @ linux exp] $ ll
2. Total 100
3. -Rw-r----- 1 oracle oinstall 94208 10-05 expdat. dmp
4. -Rw-r-- R -- 1 oracle oinstall 1090 10-05 export. log

Estimate the export time instead of actual export

1. Expdp system/oracle @ orcl directory = expFull= Y estimate = blocks estimate_only = y

1. Expdp system/oracle @ orcl directory = expFull= Y estimate =StatisticsEstimate_only = y

Export only data rows

1. Expdp system/oracle @ orcl directory = exp schemas = ing content = data_only

Export object definitions only

1. Expdp system/oracle @ orcl directory = exp schemas = ing content = metadata_only

Exclude specific object Export

1. Expdp system/oracle @ orcl directory = exp schemas = ing exclude =Table:\"= \ 'Test \'\"

Contains specific objects for export

1. Expdp system/oracle @ orcl directory = exp schemas = ing include =Table:\"Like \ 'de % \'\"

Export with where

1. Expdp system/oracle @ orcl directory = exp schemas = ing query = ing. test :\"Where rownum \ <3 \"

1 2 Next Page