Expdp/Impdp – Practical


D:\ORACLE_HOME\BIN> Impdp -help
D:\ORACLE_HOME\BIN> Expdp -help
Table Level:

E:>EXPDP  system/oracle TABLES=anar.test1,anar.test2 DIRECTORY=DAILY_BK_DIR 
DUMPFILE=test1_test2.dmp  LOGFILE=test1_test2.log
E:>EXPDP  system/oracle TABLES=hr.test1 DIRECTORY=DAILY_BK_DIR 
Day     (DD) = %date:~7,2%
Month (MM)=%date:~4,2%
Year    (YY)=%date:~10,4%
Hour   (HH)=%time:~0.2%
E:>IMPDP  TABLES=anar.test1 DIRECTORY=DAILY_BK_DIR  DUMPFILE=test1_test2.DMP LOGFILE=imp_test1test2.log
E:>IMPDP  schemas_name/password      DIRECTORY=DAILY_BK_DIR  DUMPFILE= test1_test2.DMP LOGFILE= imp_test1test2.log
E:>IMPDP  FULL=Y           DIRECTORY=DAILY_BK_DIR  DUMPFILE=anar_test1.DMP LOGFILE=imp_test1test2.log
Note: If the table already exit then you can't load with same name. You can load one table ( part ) from the dump file. 
Schema Level:
E:>EXPDP  system/password SCHEMAS=KASB_NHR  DIRECTORY=EXP_DUMP  DUMPFILE=kasb_nhr_19122012.log LOGFILE=expkbc_hist_19.log
E:>IMPDP  system/password DIRECTORY=DAILY_BK_DIR  DUMPFILE=anar.dmp LOGFILE=impanar.log
E:>IMPDP  system/password REMAP_SCHEMA=kbc:kbc2 DIRECTORY=datapump  DUMPFILE=KBC_WED-10-04-2013.DMP LOGFILE=impskbc2.log
Note: When remap_schema is use it create a new schema and load all object in its or we can load in existent schema.
Tablespace Level:
E:> EXPDP  system/password TABLESPACES=users DIRECTORY=DAILY_BK_DIR  DUMPFILE=tbsaxyz.dmp   LOGFILE=tbsxyz.log
create tablespace with same name into another database and do import
E:> IMPDP system/password TABLESPACES=users DIRECTORY=DAILY_BK_DIR DUMPFILE=tbsaxyz.dmp LOGFILE=imp_tbsxyz.log
Database Level:
E:>EXPDP  system/password DIRECTORY=datapump  DUMPFILE=fulldb.%U.dmp LOGFILE=fulldb.log parallel=9
E:>Expdp system/password full=Y directory=datapump dumpfile=DB11G.dmp logfile=expdpDB11G.log

Dictionry and Dynamic vies:
sql> Select * from v$session_longops; sql> Select * from dba_datapump_jobs; sql> Select * from dba_datapump_sessions;   # Current session during backup

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: