Oracle Data Pump (expdp and impdp)

There are only few steps required to run a Data Pump export/import utility job:

  1. Create Oracle database directory and OS directory with write/read access.

  2. Grant read and write privileges on the directory object to the database user running the export.

  3. Run expdp/impdp

--The directory path specified has to physically exist on the database server. SQL> create directory dp_dir as '/data_pump';             Directory created. SQL> select owner, directory_name, directory_path from dba_directories; OWNER                DIRECTORY_NAME                  DIRECTORY_PATH ----------        --------------------         -------------------- SYS               DP_DIR                       /data_pump SQL> grant read, write on directory dp_dir to boris;    Grant succeeded. SQL> exit

Table Level Data Pump Export/Import Syntax

TABLES parameter tells Oracle to export/import my_table and inv tables in boris schema.

$ export ORACLE_SID=TESTDB $ expdp boris/boris directory=dp_dir tables=my_table,inv dumpfile=exp.dmp logfile=exp.log $ impdp boris/boris directory=dp_dir tables=my_table,inv dumpfile=exp.dmp logfile=imp.log

Schema Level Data Pump Export/Import Syntax

$ expdp boris/boris schemas=BORIS directory=dp_dir dumpfile=BORIS.dmp logfile=expBORIS.log $ impdp boris/boris schemas=BORIS directory=dp_dir dumpfile=BORIS.dmp logfile=impBORIS.log

Please note:

- No tablespaces are included in an export.

- The password of the user will be reset by the import job.

- The import will try to recreate the user and if the user exists it will continue with a error.

- Use TABLE_EXISTS_ACTION parameter to instruct import on how to handle tables that already exist.

You can perform a schema level import  from a full export dump file:

$ impdp boris/boris directory=dp_dir dumpfile=full.dmp schemas=boris,scott

Database Level Data Pump Export/Import Syntax

Once you have a full export of a database you can replicate entire database or individual database objects.

To use full import/export DBA privileges or DATAPUMP_IMP_FULL_DATABASE role is required.

$ export ORACLE_SID=TESTDB $ expdp system/password full=Y directory=dp_dir dumpfile=DB_FUll.dmp logfile=exp.log $ impdp system/password full=Y directory=dp_dir dumpfile=DB_FUll.dmp logfile=imp.log

Please note that a full export does not export everything in the database:

- The contents of the SYS schema are not exported.

- Index data are not exported, but rather index DDL only.

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.