Oracle expdp command usage details

Oracle expdp command

Http://blog.csdn.net/zftang/article/details/6387325

Oracle expdp command usage details

Related Parameters and export examples:

 

1. Directory

Specifies the directory where the dump and log files are located
Directory = directory_object
Directory_object is used to specify the directory object name. Note that the directory object is an object created using the create directory statement, not an OS directory.
Expdp Scott/tiger directory = DMP dumpfile = A. Dump

 

Create or replace directoryDmp
As'D:/DMP'

 

Expdp zftang/zftang @ zftang directory = DMP dumpfile = test. DMP content = metadata_only

 

2. Content

This option is used to specify the content to be exported. The default value is all.
Content = {All | data_only | metadata_only}
When the content is set to all, only the object data is exported when the definition of the exported object and all its data is set to data_only. If the value is metadata_only, only the object definition is exported.
 

Expdp zftang/zftang @ zftang directory = DMP dumpfile = test. DMP content = metadata_only

---------- Export object definitions only

Expdp zftang/zftang @ zftang directory = DMP dumpfile = test. DMP content = data_only

---------- Export all data

3. dumpfile

Specifies the name of the dump file. The default name is expdat. dmp.
Dumpfile = [directory_object:] file_name [,…]
Directory_object is used to specify the directory object name, And file_name is used to specify the dump file name. Note that if directory_object is not specified, the export tool automatically uses the directory object specified by the Directory option.

Expdp zftang/zftang @ zftang directory = DMP dumpfile = test1.dmp

 

 

Steps for Data Pump tool export:

1. create directory
Create directory dir_dp as 'd:/Oracle/dir_dp ';
2. Authorization
Grant read, write on directory dir_dp to zftang;
-- View directories and permissions
Select privilege, directory_name, directory_path from user_tab_privs T, all_directories d
Where T. table_name (+) = D. directory_name order by 2, 1;
3. Execute Export
Expdp zftang/zftang @ fgisdb schemas = zftang directory = dir_dp dumpfile = expdp_test1.dmp logfile = expdp_test1.log;

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1
With the partitioning, OLAP and Data Mining options
Start "zftang". "sys_export_schema_01": zftang/******** @ fgisdb sch
Ory = dir_dp dumpfile = expdp_test1.dmp logfile = expdp_test1.log ;*/
Note:
1. Directory = dir_dp must be placed before, if placed last, will prompt ORA-39002: The operation is not valid
ORA-39070: Unable to open the log file.
ORA-39087: Invalid directory name data_pump_dir;

2. During the export process, data dump creates and uses an object named sys_export_schema_01, which is the job name used in the data dump export process, if you do not specify the name of the exported job when executing this command, a default job name is generated. If you specify the job name during the export process
Change it:
Expdp zftang/zftang @ fgisdb schemas = zftang directory = dir_dp dumpfile = expdp_test1.dmp logfile = expdp_test1.log, job_name = my_job1;
3. Do not use semicolons after the export statement. Otherwise, the job table name in the preceding Export Statement is 'my _ job1; 'instead of my_job1. Therefore, when the command expdp zftang/zftang attach = zftang. my_job1 is executed, it always prompts that the job table cannot be found.

 

 

Data Pump export modes:

1. Export in Table mode:
Expdp zftang/zftang @ fgisdb tables = zftang. B $ I _exch_info, zftang. B $ export dumpfile = expdp_test2.dmp logfile = expdp_test2.log directory = dir_dp job_name = my_job

2. Export based on query conditions:
Expdp zftang/zftang @ fgisdb tables = zftang. B $ I _exch_info dumpfile = expdp_test3.dmp logfile = expdp_test3.log directory = dir_dp job_name = my_job query = '"where rownum <11 "'

3. Export by tablespace:
Expdp zftang/zftang @ fgisdb dumpfile = expdp_tablespace.dmp tablespaces = gcomm. DBF logfile = expdp_tablespace.log directory = dir_dp job_name = my_job

4. Export Solution
Expdp zftang/zftang directory = dir_dp dumpfile = schema. dmp schemas = zftang, gwm

5. Export the entire database:
Expdp zftang/zftang @ fgisdb dumpfile = full. dmp full = y logfile = full. log directory = dir_dp job_name = my_job

Impdp import mode:

1. Import by table
The table in the p_street_area.dmp file, which is exported by the gwm user according to schemas = gwm:
Impdp gwm/gwm @ fgisdb dumpfile = p_street_area.dmp logfile = imp_p_street_area.log directory = dir_dp tables = p_street_area job_name = my_job

2. Import by user (user information can be directly imported if user information does not exist)
Impdp gwm/gwm @ fgisdb schemas = gwm dumpfile = expdp_test.dmp logfile = expdp_test.log directory = dir_dp job_name = my_job

3. Directly import the DMP file without using the expdp step:
-- Import the table p_street_area from the source database to the target database
Impdp gwm/gwm directory = dir_dp network_link = igisdb tables = p_street_area logfile = p_street_area.log job_name = my_job
Igisdb is the link name between the target database and the source data, and dir_dp is the directory on the target database.

4. Change the tablespace
Use the remap_tablespace Parameter
-- Export all data under the gwm user
Expdp system/orcl directory = data_pump_dir dumpfile = gwm. dmp schemas = gwm
Note: If the user data is exported by the Sys user, including user creation and authorization, it is not included when exported by the user.
-- The following describes how to import all data under the gwm user to the gcomm table space (originally under the gmapdata table space ).
Impdp system/orcl directory = data_pump_dir dumpfile = gwm. dmp remap_tablespace = gmapdata: gcomm