Oracle expdp

Oracle data pump export has been a new utility for backing up data replacing the old “exp” utility. “Expdp” has a lot of new and important features in taking a backup, it is must faster and reliable. However as in “exp” utility you cannot get a backup to a remote location, backups are taken only to the specific server (where the DB you need to backup resides).

Basic Example

/*Exporting the source DB */

userid=system/<system password>@//<ip>/<db> /*Source DB details*/
dumpfile=testdump.dmp /*Name of dump file*/
logfile=testdump.logĀ /*Name of log file*/
full=y /*Type of dump (all schema/ only specific schema(s)*/
directory=testdirectory /*Directory where dump is created*/

/*Importing the destination DB */

More Examples

1) Full dump

expdp userid=system/password@SOURCE directory=DIR dumpfile=fullexample1.dmp logfile=expdpexample1.log full=y
impdp userid=system/password@TARGET dumpfile=fullexample1.dmp logfile=impdpexample1.log full=y directory=DIR


ALL – Imports both data and metadata to destination DB (Default)
METADATA_ONLY – Imports only object definitions, no table data
DATA_ONLY – Imports only table data, no object definitions

2) Table dump

expdp userid=system/password@SOURCE tables=user1.example2 directory=DIR dumpfile=table1.dmp logfile=expdpexample2.log
impdp userid=system/password@TARGET directory=DIR dumpfile=table.dmp full=y logfile=impdptable.log

expdp userid=mubasher_oms/password@SOURCE tables=example2 directory=DIR dumpfile=table2.dmp logfile=expdptable2.log
impdp userid=system/password@TARGET directory=DIR dumpfile=table2.dmp full=y logfile=impdptable2.log TABLE_EXISTS_ACTION=APPEND


APPEND – Extra records in source table imported
REPLACE – Records are updated from the source table
TRUNCATE – Table transformed as source

3) Schema dump

expdp userid=system/password@SOURCE schemas=schema1 directory=DIR dumpfile=schema1.dmp logfile=expdpschema1.log
impdp userid=system/password@TARGET full=y directory=DIR dumpfile=schema1.dmp logfile=impdpschema1.log

/*Exports only a particular schema*/



/*This option is used when you are required to export particular objects*/

Only tables
expdp userid=system/password@SOURCE schemas=schema2 include=TABLE directory=DIR dumpfile=include1.dmp logfile=expdpinclude1.log

Only tables and procedures
expdp userid=system/password@SOURCE schemas=schema2 include=TABLE include=PROCEDURE directory=DIR dumpfile=include2.dmp logfile=expdpinclude2.log

expdp userid=system/password@SOURCE schemas=schema2 include=TABLE,PROCEDURE directory=DIR dumpfile=include3.dmp logfile=expdpinclude3.log

Only table “Prac1”
expdp userid=system/password@SOURCE schemas=schema2 include=TABLE:”\=’PRAC1′” directory=DIR dumpfile=include4.dmp logfile=expdpinclude4.log

Only tables that start with “P”
expdp userid=system/password@SOURCE schemas=schema2 include=TABLE:”LIKE ‘P%'” directory=DIR dumpfile=include5.dmp logfile=expdpinclude5.log

Only tables “PRAC1” & “PRAC2”
expdp userid=system/password@SOURCE schemas=schema2 include=TABLE:”IN (‘PRAC1′,’PRAC2’)” directory=DIR dumpfile=include6.dmp logfile=expdpinclude6.log


Exclude all tables
expdp userid=system/password@SOURCE schemas=schema2 exclude=TABLE directory=DIR dumpfile=exclude1.dmp logfile=expdpexclude1.log

Exclude table “PRAC1”
expdp userid=system/password@SOURCE schemas=schema2 exclude=TABLE:”\=’PRAC1′” directory=DIR dumpfile=exclude2.dmp logfile=expdpexclude2.log

Import only meta data of all tables except “PRAC1”
expdp userid=system/password@SOURCE schemas=schema2 exclude=TABLE:”\=’PRAC1′” directory=DIR dumpfile=exclude3.dmp logfile=expdpexclude3.log content=metadata_only

5) Network Exports/Imports (NETWORK_LINK)

grant IMP_FULL_DATABASE to source_schema1;
grant IMP_FULL_DATABASE to destination_schema1;
grant EXP_FULL_DATABASEĀ  to destination_schema1;

impdp userid=source_schema1/password@TARGET DIRECTORY=DIR NETWORK_LINK=REMOTE_LINK

*NETWORK_LINK=DB link to Source DB (Here, impdp connects to the source DB, gets the required data and writes it directory to the destination DB)/

6) Query dump

/*Export data for specific conditions only (all data where st_id=1 in Prac1 table*/

expdp userid=mubasher_test2/password@SOURCE directory=DIR dumpfile=query1.dmp logfile=query1.log tables=prac1 query=prac1:\”where st_id=1\”

7)Remap schemas

/*Change the schema name in the destination DB */

expdp userid=system/password@SOURCE schemas=schema2 directory=DIR dumpfile=remapschema1.dmp logfile=expdpremapschema1.log
impdp userid=system/password@TARGET full=y directory=DIR dumpfile=remapschema1.dmp logfile=impdpremapschema1.log remap_schema=schema2 :test2

8)Remap tablespaces

/*Change the schema name in the destination DB */

expdp userid=system/password@SOURCE schemas=schema2 directory=DIR dumpfile=remapdatafiles1.dmp logfile=expdpremapdatafiles1.log
impdp userid=system/password@TARGET full=y directory=DIR dumpfile=remapdatafiles1.dmp logfile=impdpremapdatafiles1.dmp remap_schema=schema2 :test2 remap_tablespace=schema2_ts:test2_ts

9) Estimate

/*Get an estimation of the dump*/

expdp userid=system/password@SOURCE schemas=mubasher_test2 estimate_only=y
expdp userid=system/password@SOURCE schemas=mubasher_test2 estimate_only=y estimate=blocks
expdp userid=system/password@SOURCE schemas=mubasher_test2 estimate_only=y estimate=statistics

10) Files

/*Export data into more than one dump file depending on size*/

expdp userid=system/password@SOURCE full=y dumpfile=file%U.dmp filesize=15M directory=DIR logfile=expdpfile.log
impdp userid=system/password@TARGET full=y directory=DIR dumpfile=file%U.dmp logfile=impdpfile.log

11)Attach and detach

/*Exports can be stopped, paused, resumed accordingly*/

expdp userid=system/password@SOURCE directory=DIR dumpfile=attach1.dmp logfile=expdpattach1.log full=y job_name=fullexport

Export> STOP_JOB=IMMEDIATE /*pause the export*/

expdp userid=system/password@SOURCE attach=fullexport

Export> CONTINUE_CLIENT /*continue with the export*/
Export> KILL_JOB /*kill the export*/

12) Version

/*Export data when destination DB version is not similar to source DB*/

expdp userid=system/password@SOURCE directory=DIR dumpfile=fullexample1.dmp logfile=expdpexample1.log full=y version=10.2

For more information :