Preface
Expdp and impdp is to move data between the oracle database tools, expdp impdp should only be used in the database server and cannot be used on the client side, this article briefly summarizes the expdp and impdp commonly used commands, details refer to the oracle Utilities, official document,
Directory relevant SQL statements:
Select * from dba_directories;
Create the directory my_dir as'/home/oracle/TMP ';
Grant read, write on directory my_dir to Scott;
EXPDP export
Note:
1, derivative database user needs to have read and write access to directory_object,
In 2, the operating system already exists directory_object specified path,
3, oracle users have to directory_object specified path of read and write access,
4, system users to export, will create the user and the system permissions granted also export the metadata, ordinary users unable to export the metadata,
Expdp command example
# # export table, example:
Expdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=expdp. The log tables=Scott. The emp
# # export table, example:
Expdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=expdp. The log tables=\ [Scott. The emp, Scott. Dept \)
# # to export a user (export all the user object), example:
Expdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=expdp. Log schemas=Scott
# # export multiple users, example:
Expdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=expdp. Log schemas=\ [Scott, hr \)
# # to export the entire database (sys, ordsys mdsys user data will not be exported) example:
Expdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=expdp. Log full=yes
As an example to export a user
# # parallel export:
Expdp system/oracle directory=my_dir dumpfile=expdp % U.d mp logfile=expdp. The log schemas=Scott parallel=5
# # export user metadata (contain the table definition, stored procedures, functions, etc.) :
Expdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=expdp. Log schemas=Scott content=metadata_only
# # export user stored procedure, example:
Expdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=expdp. Log schemas=Scott include=procedure
# # user function is derived and the view, example:
Expdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=expdp. Log schemas=Scott include=\ [function, view \)
# # to export a user, but does not include index, example:
Expdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=expdp. Log schemas=Scott exclude=index
Expdp parameters description:
The attach=[schema_name] job_name
Note: nodefault, connected to the operation, enter the interactive mode,
Mutually exclusive export mode, the following five parameters,
Full=[yes | no]
Note: nodefault, export all data and metadata, to perform a complete export, needs to have datapump_exp_full_database role,
Schemas=schema_name, [...].
Note: the default current user 's schema, export user,
Table_name tables=[schema_name.] [: partition_name], [...].
Note: nodefault, export table,
Tablespaces=tablespace_name [,...].
Note: nodefault, export table space,
Transport_tablespaces=tablespace_name [,...].
Note: nodefault, export the movable table space,
Filter conditions, the following three parameters mutually exclusive:
Query=[schema.] [table_name:] query_clause
Description: nodefault, export according to the query conditions,
Exclude=object_type [: name_clause] [,...].
Note: nodefault, exclude specific object types,
Include=object_type [: name_clause] [,...].
Description: nodefault, including specific object types,
Other parameters:
The directory=directory_object
Note: default: data_pump_dir, export path,
File_name dumpfile=[directory_object:] [,...].
Note: default: expdat DMP, the exported file name,
Logfile=[directory_object:] file_name
Default: export. The log, the export of log filename,
The content=[all | data_only | metadata_only]
Note: default: all, assigned to export data,
The parallel=integer
Note: default: 1, parallelism, this value should be less than or equal to number of DMP file, or can be used for 'dumpfile=' substitution variables' % U ',
RAC environment, the parallelism is greater than 1, pay attention to the catalog should be Shared directory,
Compression will=[all | data_only | metadata_only | none]
Note: default: metadata_only, compression,
Parfile=[directory_path] file_name
Note: nodefault, export parameters specified file name,
Network_link=source_database_link
Note: nodefault, connected to the source database export,
Filesize=integer [b | | KB | MB | gb TB]
Note: default: 0 without limiting size, specify the maximum size of each DMP file,
If this parameter is less than will export the size of the data, the error ORA - 39095,
Job_name=jobname_string
Note: default: the system - the name of the form SYS_EXPORT_ & lt; Mode> _NN, assigned the job name,
Version=[compatilble | latest | version_string]
Default: compatible, the default compatibility mode, you can specify export DMP file version,
IMPDP import
Note:
1, expdp documents cannot use imp import export, only through impdp import database,
2, when import existing object, the default will skip this object, continue to import the other objects,
3, import should make sure the DMP file and the target database in tablespace, schema corresponds to,
4, import the DMP file, should determine the DMP file export orders, in order to import the data smoothly,
Get a DMP file, if you forget the export orders, can through the following methods to confirm (unofficial, do not use on the production data) :
Make sure the DMP file is exp export or expdp export
1) XXD test. DMP | more
Expdp exported file beginning of 0301, exp the exported file beginning with 0303
2) strings test. DMP | more
Expdp exported DMP file header information:
"SYS". "SYS_EXPORT_TABLE_01" -- -- -- -- -- the job name
X86_64/Linux 2.4 xx - operating system version
BJDB -- -- -- -- -- the database name
ZHS16GBK -- -- -- -- -- the database character set
11.02.00.04.00 -- -- -- -- -- the database version
Exp DMP export file header information:
IEXPORT: V11.02.00 version -- -- -- -- --
USCOTT -- -- -- -- -- the user
RTABLES object -- -- -- -- --
Confirm expdp DMP export file export command
Strings test. DMP | grep CLIENT_COMMAND
Impdp command example
# # import all data in the DMP file, example:
Impdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=impdp. Log full=yes
# # import table, example:
Impdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=impdp. The log tables=Scott. The emp
# # import table, example:
Impdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=impdp. The log tables=\ [Scott. The emp, Scott. Dept \)
# # import a user, example:
Impdp system/oracle directory=my_dir dumpfile=expdp. DMP logfile=impdp. Log schemas=Scott
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
