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 */
expdp
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
CONTENT=METADATA_ONLY
*CONTENT={ALL / METADATA_ONLY / DATA_ONLY}
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
*TABLE_EXISTS_ACTION={APPEND / REPLACE / TRUNCATE}
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*/
4) INCLUDE and EXCLUDE
INCLUDE
/*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
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 : http://docs.oracle.com/cd/B14117_01/server.101/b10825/dp_export.htm#i1006388
Database World 