oracle11g EXPDP IMPDP Command detailed

Things to be aware of when using EXPDP and IMPDP:

Exp and IMP are client tool programs that can be used either on the client or on the server side.

EXPDP and IMPDP are server-side utilities that can only be used on the Oracle server and not on the client side.

IMP is only available for exp exported files, not for EXPDP export files, IMPDP only for EXPDP exported files, not for exp export files.

EXPDP or IMPDP command, you can temporarily not indicate the username/password @ instance name as identity, and then follow the prompts to enter, such as:

EXPDP Schemas=scott dumpfile=expdp.dmp directory=dpdata1;

To create a logical directory, the command does not create a real directory in the operating system, preferably created by an administrator such as system.

Create directory dpdata1 as ' D:\test\dump ';

Second, view the Management Manager directory (and see if the operating system exists, because Oracle does not care if the directory exists, and if it does not exist, an error occurs)

SELECT * from Dba_directories;

Third, give the Scott user permissions to operate in the specified directory, preferably by a system administrator.

Grant Read,write on directory dpdata1 to Scott;

Iv. Export of data

1) According to the user guide

EXPDP Scott/[email protected] Schemas=scott dumpfile=expdp.dmp directory=dpdata1;

2) Parallel Process parallel

EXPDP Scott/[email protected] directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3

3) According to the table name guide

EXPDP Scott/[email protected] tables=emp,dept dumpfile=expdp.dmp directory=dpdata1;

4) Guided by query criteria

EXPDP Scott/[email protected] directory=dpdata1 dumpfile=expdp.dmp tables=emp query= ' WHERE deptno=20 ';

5) According to the Table space Guide

EXPDP System/manager directory=dpdata1 dumpfile=tablespace.dmp tablespaces=temp,example;

6) Guide the entire database

EXPDP System/manager directory=dpdata1 dumpfile=full.dmp full=y;

Five, restore data

1) leads to the specified user

IMPDP Scott/tiger directory=dpdata1 dumpfile=expdp.dmp Schemas=scott;

2) Change the owner of the table

IMPDP system/manager directory=dpdata1 dumpfile=expdp.dmp tables=scott.dept remap_schema=scott:system;

3) Import Table space

IMPDP System/manager directory=dpdata1 dumpfile=tablespace.dmp tablespaces=example;

4) Import the database

impdb System/manager directory=dump_dir dumpfile=full.dmp full=y;

5) Append Data

IMPDP system/manager directory=dpdata1 dumpfile=expdp.dmp schemas=system table_exists_action

oracle11g EXPDP IMPDP Command detailed