Oracle Data Pump Export / Import Examples

1. Introduction

This post show step-by-step how to configure DataPump Directory, how to Export and how to Import using.

1.1. Pre-requisites

Oracle Data Pump tool requires a directory configured on database. Example

2. Examples

2.1. Create Directory for DataPump Export/Import and adjust permission

SQL> CREATE DIRECTORY DPUMP_BACKUP AS '/u01/backup/dpump';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY DPUMP_BACKUP TO SYSTEM, SYS;

Grant succeeded.

2.2. Create Physical DataPump Directory

# mkdir /u01/backup
# mkdir /u01/backup/dpump
# chown -R oracle:dba /u01/backup/

2.3. Example #1: Export DataPump Full Database mode

$ expdp "'/ AS SYSDBA'" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_FULL.dmp LOGFILE=expdp_FULL.log FULL=Y REUSE_DUMPFILES=Y DATA_OPTIONS=XML_CLOBS

Export: Release 11.2.0.2.0 - Production on Fri Sep 5 23:27:57 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_FULL.dmp LOGFILE=expdp_FULL.log FULL=Y REUSE_DUMPFILES=Y DATA_OPTIONS=XML_CLOBS

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 164.3 MB

Processing object type DATABASE_EXPORT/TABLESPACE

      :

      :

      :

. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows

Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_FULL_01 is:

  /u01/backup/dpump/expdp_FULL.dmp

Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 23:29:28

2.4. Example #2: Export DataPump some Schemas Database mode

$ expdp "'/ AS SYSDBA'" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_FULL.dmp LOGFILE=expdp_FULL.log SCHEMAS=RM,SYSDBA,RM_INTEG,MANAGER,MANAGER_WEBAPP,INBI,INBI2,CONFLUENCE,CONFLUENCE_GTW,TRACKPLUS,FIRESCRUM,PYTHON_APP,SURVEY_APP,PRJBUILDER,PROJECTBUILDER,PBOWNER REUSE_DUMPFILES=Y DATA_OPTIONS=XML_CLOBS

Export: Release 11.2.0.3.0 - Production on Thu Sep 18 14:14:58 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" DIRECTORY=DPUMP_BACKUP DUMPFILE=expdp_full.dmp LOGFILE=expdp_full.log SCHEMAS=RM,SYSDBA,RM_INTEG,MANAGER,MANAGER_WEBAPP,INBI,INBI2,CONFLUENCE,CONFLUENCE_GTW,TRACKPLUS,ALEXJUNQ,FIRESCRUM,PYTHON_APP,SURVEY_APP,PRJBUILDER,PROJECTBUILDER,PBOWNER REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12.51 GB

Processing object type SCHEMA_EXPORT/USER

      :

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/backup/dpump/expdp_full.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:32:40

3. References

• http://www.oracle.com/technetwork/issue-archive/2009/09-jul/datapump11g2009-quickstart-128718.pdf
• http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm#g1022624
• http://www.nyoug.org/Presentations/2006/September_NYC_Metro_Meeting/200609Nanda_Data%20Pump.pdf