EXPDP and IMPDP utility

Expdp and Impdp utility are datapump utilities They are used to take logical backup of the database. They can be used to take database level, schema level, object level , tablespace level backups.

Command for Full Database export :-

C:\Users\NewAdmin> expdp system/orcl directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y &   ----- (if database is 11g)

C:\Users\NewAdmin> expdp system/orcl directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=METADATA_ONLY full=y &   ----- (if database is 10g)

############# Compression = all is not available in 10g version##############

Command for Full Database import :-

1. First create all the tablespaces which are there in old database either with same name or with different name. If tablespace names are different then remap_tablespace has to be used.

2. If you want to create new users then while importing remap_schema has to be used, otherwise it will create users with the same name.

Schema with same name and tablespaces with same name as they are in old database then PFB command to import full database  :-

C:\Users\NewAdmin> impdp system/test directory=datatest dumpfile=expdp_full_29062013.dmp logfile=impdp_full_29062013.log full=y

Schema and tablespaces with different name then PFB command to import full database :-

C:\Users\NewAdmin> impdp system/test directory=datatest dumpfile=expdp_full_29062013.dmp logfile=impdp_full_29062013.log full=y remap_schema= amit:test,amit1:test1,amit2:test2 remap_tablespace=amit_tbs:test_tbs,amit1_tbs:test1_tbs,amit2_tbs:test2_tbs &

Schema Level Export :-

C:\Users\NewAdmin>expdp amit/amit directory=datapump dumpfile=expdp_schema_29062013.dmp logfile=expdp_schema_29062013.log compression=all &

Schema level import :-

If we want to import Amit schema contents to test schema contents and that to in tablespace of test schema the PFB command :-

C:\Users\NewAdmin>impdp test/test directory=datapump dumpfile=expdp_schema_29062013.dmp logfile=impdp_schema_29062013.log remap_schema=srilanka:test remap_tablespace=amit_tbs:test_tbs &

Schema Level export without data :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_without_data_29062013.dmp logfile=expdp_without_data_29062013.log content=METADATA_ONLY compression=all &

Schema Level export without metadata :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_without_data_29062013.dmp logfile=expdp_without_data_29062013.log content=DATA_ONLY compression=all &

Table Level Export :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_table_29062013.dmp logfile=expdp_table_29062013.log tables=temp,temp1 compression=all &  

Table Level Import :-

C:\Users\NewAdmin> impdp test/test directory=datapump  dumpfile=expdp_table_29062013.dmp logfile=impdp_table_29062013.log remap_schema=amit:test &

Table Level Import from a schema or database level dump :-

impdp test/test schemas=amit directory=datapump dumpfile=exp_am.dmp remap_schema=amit:test INCLUDE=TABLE:\"IN \(\'DB_T_ALARMS_TTT\'\)\"

Exclude some table or index from a schema level export :-

C:\Users\NewAdmin> expdp amit/amit directory=datapump dumpfile=expdp_exclude_29062013.dmp logfile=expdp_exclude_29062013.log exclude=table:\"in\(\'temp\',\'temp1\'\)\" compression=all &

Exclude Some Schema and some tables from a Full Database Export :-

C:\Users\NewAdmin>expdp system/orcl directory=datapump dumpfile=expdp_exclude_29062013.dmp logfile=expdp_exclude_29062013.log exclude=table:\"in\(\'temp\',\'temp1\'\)\" exclude=schema:\"in\(\'AMIT\'\)\" compression=all full=y &

I hope this article helped you.

Regards,
Amit Rath