Detailed explanation of using oracle expdp command based on oracle
- 2021-07-06 12:00:15
- OfStack
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
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