Detailed explanation of using oracle expdp command based on oracle

  • 2021-07-06 12:00:15
  • OfStack

Specify the directory where the dump files and log files are located
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

This option is used to specify what to export. The default value is ALL
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
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:
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; */
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

Related articles: