Using Oracle Data Pump for import/export

In order to get Data Pump directory location, execute

SELECT directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR'; 

Typically it is $ORACLE_BASE/admin/DBName/dpdump/

Then check priviges that are assigned to the directory by executing;

SELECT grantee, privilege FROM dba_tab_privs dtp WHERE table_name = 'DATA_PUMP_DIR';

The grantee could be a user or a role.

Use the following to create a directory:

CREATE OR REPLACE DIRECTORY dpump_dir1 AS '.../admin/DBName/dpdump';

After a directory is created, you need to grant READ and WRITE permission on the directory to other users. For example, to allow the Oracle database to read and to write to files on behalf of user usr1 in the directory named by dpump_dir1, you must execute the following command:

GRANT READ,WRITE ON DIRECTORY dpump_dir1 TO usr1;

Once the directory access is granted, the user can export his database objects with command arguments from a command line:

expdp usr1/pwd DIRECTORY=dpump_dir1 dumpfile=usr1.dmp

The above will produce the following output:

Export: Release 11.2.0.3.0 - Production on Tue Jan 8 12:48:31 2013

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 "USR1"."SYS_EXPORT_SCHEMA_01":  usr1/******** DIRECTORY=dpump_dir1 dumpfile=usr1.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USR1"."TABLE1"                             0 KB       0 rows
. . exported "USR1"."TABLE2"                             0 KB       0 rows
. . exported "USR1"."TABLE3"                                0 KB       0 rows
Master table "USR1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USR1.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/DBName/dpdump/usr1.dmp
Job "USR1"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:49:08

The above will also be written to a default log file in the same directory - export.log or in the log you specify in the command line in LOGFILE parameter:

expdp usr1/pwd DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log

You could also specify particular tables you want to export in a TABLES parameter of your command, in case you do not need all user tables. You can add more than one table separated by comma:

expdp usr1/pwd TABLES=TABLE1,TABLE2 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log

Import command would look something like this:

impdp usr1/pwd TABLES=TABLE1,TABLE2 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=import_usr1.log

To import a schema, you will need to add a SCHEMAS parameter to your command:

expdp usr1/pwd SCHEMAS=USR1 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log



impdp usr1/pwd SCHEMAS=USR1 DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=import_usr1.log

And for a full database import/export, you will need to add FULL parameter and set it to y:

expdp usr1/pwd FULL=y DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=export_usr1.log



impdp usr1/pwd FULL=y DIRECTORY=dpump_dir1 DUMPFILE=usr1.dmp LOGFILE=import_usr1.log

To append data to existing tables, add parameter TABLE_EXISTS_ACTION=APPEND to your import statement