Monish's DBA Blog

Datapump

Datapump is a server based bulk data movement infrastructure that supersedes the old import and export utilities. The old export/ import tools are still available, but do not support all Oracle 10g and 11g features. The new utilities are named expdp and impdp. It is ideal for large databases and data warehousing environments, where high-performance data movement offers significant time savings to database administrators.

Create database directories

Execute the following commands to create a database directory. This directory must point to a valid directory on the same server as the database:

SQL> CREATE DIRECTORY dmpdir AS ‘/opt/oracle’;

Directory created.

SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;

Grant succeeded.

Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:

SQL> SELECT directory_path FROM dba_directories WHERE directory_name = ‘DATA_PUMP_DIR’;

DIRECTORY_PATH
—————————————————————————–
/app/oracle/product/10.2.0/rdbms/log/

Start using datapump export

$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

Import into another database

impdp system/oracle DIRECTORY=dmpdir DUMPFILE=scott.dmp

Table Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

The DBA_DATAPUMP_JOBS view can be used to monitor the current jobs:

> select * from dba_datapump_jobs;

To list all command line parameters, type:

expdp help=yes
impdp help=yes