This topic describes how to use the Oracle data pump and how to use the data pump command

Expdp and IMPDP

Exp and IMP are client tools that can be used on both the client and server side.

2. Expdp and IMPDP are server tools. They can only be used on the Oracle server, not the client.

Imp only applies to exp export files, not expDP export files; Impdp only applies to expDP exported files, not exp exported files.

4. Expdp is used instead of expDP on servers with more than 10GB.

Create a logical directory. This command does not create a real directory on the operating system. You are advised to create a logical directory as an administrator such as system.

$ conn system/manger@orcl as sysdbaCopy the code
create directory dump_dir as 'd:\test\dump';Copy the code

2. Check the administrator directory (also check whether the operating system exists, because Oracle does not care whether the directory exists, if not, error)

select * from dba_directories;Copy the code

Grant the operation permission to user Scott in the specified directory. It is better to grant the operation permission to the administrator such as system.

grant read,write on directory dump_dir to scott;Copy the code

Use EXPDP to export data

1) Export users
$ expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp directory=dump_dirCopy the code
2) derived tables
$ expdp scott/tiger@orcl tables=emp,dept dumpfile=expdp.dmp directory=dump_dirCopy the code
3) Guide according to query conditions
$ expdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp tables=emp query='where deptno=20'Copy the code
4) Derivatives by tablespace
$ expdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=temp,exampleCopy the code
5) Guide the entire database
$ expdp system/manager@orcl directory=dump_dir dumpfile=full.dmp full=yCopy the code
6) Export only the table structure

Add one more parameter: Content = metadATA_only

Import data with IMPDP

Import command:

$impdp user/password @ database folder =.. dumpfile=... Remap_tablespace = XXX: XXXX; remap_tablespace= XXX: XXXX; TABLE_EXISTS_ACTION Scheme in which the table existsCopy the code

Chestnut illustration:

$ impdp gomyck/hy123123@orcl directory=dump_dir dumpfile=PMS1.dmp REMAP_SCHEMA=olduser:newuser remap_schema=SCYW:gomyck remap_tablespace=TS_SBTZ:GOMYCK TABLE_EXISTS_ACTION=TRUNCATE Copy the code

TABLE_EXISTS_ACTION description:

Default: SKIP(but if CONTENT=DATA_ONLY, APPEND is default)

Action: defines impDP action if the table to be imported already exists

Values and their meanings:

SKIP: SKIP tables that already exist

APPEND: Keeps existing data unchanged and imports source data

TRUNCATE: Deletes existing data and imports source data

REPLACE: Delete the existing table, rebuild it and import the source data