Posted by: Monish | December 29, 2008

Oracle 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’;


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



  1. Hi Monish,

    I am interested in getting trained for Oracle 11g DBA. Do you offer training? Kindly get in touch with me via email. Hope to hear from you soon.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s


%d bloggers like this: