Oracle Data Pump in Oracle Database

Oracle Database have new Data Pump utility, which is faster and more flexible as compaire to the "exp" and "imp" utilities used in old Oracle database releases. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables. Here some simple data pump examples using expdp and impdp utility.

1. Create a DIRECTORY, Grant on DIRECTORY (useing directory name as "datadir","datadump")

Create directory at the OS Level

$ cd /oradisk
$ mkdir datadump

$ sqlplus /nolog
SQL> connect /as sysdba

SQL> DROP DIRECTORY datadump;
SQL> CREATE DIRECTORY datadump AS '/oradisk/datadump';
SQL> GRANT READ,WRITE ON DIRECTORY datadump TO HR;

Export the EMPPLOYEE table from the HR user

$ expdp hr/hr directory=datadump tables=hr.employee
SQL> Drop table employee purge;

Import the .DMP file back into the HR user.

$ impdp hr/hr directory=datadump tables=employee

Check employee table after completing import.

$ sqlplus hr/hr
Sql> select * from employee;

1. Examples: Performing a table mode export

expdp system/ TABLES=sh.costs,sh.sales DUMPFILE=datadir:table.dmp NOLOGFILE=Y

2. Examples: Performing a table mode export
expdp system/oracle SCHEMAS=sh,hr DUMPFILE=datadir:schema%U.dmp LOGFILE=datadir:expschema.log

3. Example - Performing a Parallel Full Database Exportexpdp system/oracle
FULL=Y DUMPFILE=datadir:fullexp%U.dmp, FILESIZE=2g PARALLEL=4 LOGFILE=datadir:expfull.log JOB_NAME=expfull

The FULL parameter indicates that the export is a full database mode export. All data andmetadata in the database are exported.

The PARALLEL parameter specifies the maximum number of threads of activeexecution operating on behalf of the export job. This parameter enables you to maketrade-offs between resource consumption and elapsed time. For best performance, thevalue specified for PARALLEL should be at least as large as the number of output filesspecified with the DUMPFILE parameter. Each Data Pump execution thread writesexclusively to one file at a time.

The PARALLEL parameter is valid only in the Enterprise Edition of the Oracle database.To increase or decrease the value of PARALLEL during job execution, use interactivecommandmode that is described in the example below.The FILESIZE parameter will limit the maximum size of each dump file to 2 gigabytes.

You can query DBA_DATAPUMP_JOBS to monitor the current jobs.

SQL> select * from dba_datapump_jobs;

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used all objects except those specified by it will be included in the export.

expdp scott/tiger schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" DUMPFILE:SCOTT.dmp LOGFILE=datadump:expdpSCOTT.log
expdp scott/tiger schemas=SCOTT exclude=TABLE:"= 'BONUS'" DUMPFILE:SCOTT.dmp LOGFILE=datadump:expdpSCOTT.log