1. DIRECTORY Specify the directory where the dump files and log files are located DIRECTORY=directory_object Directory_object is used to specify the directory object name. Note that the directory object is created using the CREATE DIRECTORY statement, not the OS directory Expdp scott/tiger DIRECTORY= DMP DUMPFILE=a.dump
create or replace directory dmp as ‘d:/dmp’
expdp zftang/zftang@zftang directory=dmp dumpfile=test.dmp content=metadata_only
2. CONTENT This option is used to specify what to export. The default value is ALL CONTENT={ALL | DATA_ONLY | METADATA_ONLY} When CONTENT is set to ALL, the object definition and all its data are exported. When DATA_ONLY, only the object data is exported. When METADATA_ONLY, only the object definition is exported
expdp zftang/zftang@zftang directory=dmp dumpfile=test.dmp content=metadata_only --------Export only object definitions expdp zftang/zftang@zftang directory=dmp dumpfile=test.dmp content=data_only -----All data exported 3. DUMPFILE Used to specify 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
To export the data pump tool: 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. Perform the 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; */ Remarks: 1. directory=dir_dp must be placed first. If it is placed last, ORA-39002 will be prompted: Operation is invalid ORA-39070: Unable to open log file. ORA-39087: Directory name DATA_PUMP_DIR; Invalid
2. In the export process, DATA DUMP creates and uses an object named SYS_EXPORT_SCHEMA_01, which is the JOB name used in the export process of DATA DUMP. If the export JOB name is not specified when executing this command, a default JOB name will be generated. If the export JOB name is specified during the export process, it will appear with the specified name Replace as follows: expdp zftang/zftang@fgisdb schemas=zftang directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log,job_name=my_job1; 3. Do not have a semicolon after the export statement, otherwise the name of the job table in the above export statement is’ my_job1; ’, instead of my_job1. Therefore, when expdp zftang/zftang attach=zftang.my_job1 executes this command, 1 prompts that the job table cannot be found
Various modes derived from the data pump: 1. Export by table schema: expdp zftang/zftang@fgisdb tables=zftang.b$i_exch_info,zftang.b$i_manhole_info dumpfile =expdp_test2.dmp logfile=expdp_test2.log directory=dir_dp job_name=my_job 2. Export by query criteria: 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 the scheme 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 Tables in the p_street_area. dmp file, which was exported by the gwm user as 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 imported directly, that is, if user information does not exist, it can also be imported directly) impdp gwm/gwm@fgisdb schemas=gwm dumpfile =expdp_test.dmp logfile=expdp_test.log directory=dir_dp job_name=my_job 3. The method of directly importing dmp files without generating expdp steps: —Importing 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 destination database and the source data, and dir_dp is the directory on the destination database 4. Change the table space Adopt remap_tablespace parameter —Export all data under gwm users expdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp SCHEMAS=gwm Note: If the user data is exported by sys user, including user creation and authorization, these contents are not included when exported by self-user —The following is to import all the data under gwm user into the tablespace gcomm (originally under gmapdata tablespace) impdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp remap_tablespace=gmapdata:gcomm