Data pump examples provided by ORADBA

 

 

Comments for data pump: use data pump is you need speed. It is available since 10gR2.
Data pump needs DIRECTORY objects to be set up first which are used to specify the
source and destination folders for data pump and typically contain log and dump files.

 

Creating data pump directories

Creating source and destination database DIRECTORY objects:

SQL> CREATE OR REPLACE DIRECTORY dmp_dir AS '/u04/oracle/dpump';

SQL> select * from dba_directories;

 

If you have separate user to execute data pump operations you’ll also need:

SQL> GRANT EXP_FULL_DATABASE  to DPUSER;

SQL> GRANT READ, WRITE ON DIRECTORY dmp_dir to DPUSER;

 

Data pump schema export

1) Check if database DIRECTORY objects are set up correctly, then execute export:

 

SQL> select * from dba_directories where DIRECTORY_NAME='DMP_DIR';

 

oracle@host> expdp SYSTEM@dbname schemas=SCHEMA_NAME directory=DMP_DIR dumpfile=SCHEMA_NAME.dmp logfile=SCHEMA_NAME_expdp.log

 

oracle@host> expdp SYSTEM@dbname tables=SCOTT.EMP directory=DMP_DIR dumpfile=EMP.dmp logfile=EMP_expdp.log

 

oracle@host> expdp DPUSER@dbname tables=SCOTT.EMP directory=DMP_DIR dumpfile=EMP.dmp logfile=expdpEMP.log

 

 

The three examples above show the basic use of expdp for schema export, table

export  and export as non system user called DPUSER

 

 

Data pump schema import

Don’t forget that you need DIRECTORY objects in the database for data pump!

 

1) Check if the dmpdir DIRECTORY exists, if not create it first.

 

SQL> select * from dba_directories where DIRECTORY_NAME='DMP_DIR';

SQL> CREATE OR REPLACE DIRECTORY dmp_dir AS '/u04/oracle/dpump';

 

2) Put the export data pump file to the OS directory specified and load into the DB:

oracle@host> impdp system directory=dmp_dir dumpfile=schemaDPEXP.dump schemas=SCHEMANAME logfile=SCHEMANAME_imp.log

 

If you want to import into a different schema use the remap_schema parameter:

 

oracle@host> impdp system directory=dmp_dir dumpfile=schemaDPEXP.dump schemas=SCHEMANAME logfile=SCHEMANAME_imp.log

remap_schema= SOURCE_SCHEMA:TARGET_SCHEMA

 

 

Rounded Rectangle: Home Page