Data Pump


Refrences:
1. Oracle Database 10g: New Features for Administrators 
Chapter # 3: Load and Unload Data
2. http://orafaq.com/node/12
3. http://www.oracle.com/technology/pub/notes/technote_pump_restart.html
Practical:
D:\ORACLE_HOME\BIN> Impdp -help
D:\ORACLE_HOME\BIN> Expdp -help
Table Level:
E:>EXPDP system/oracle TABLES=scott.emp,scott.dept DIRECTORY=DAILY_BK_DIR DUMPFILE=emp_dept.dmp 1LOGFILE=emp_dept.log
E:>EXPDP system/oracle TABLES=hr.emp DIRECTORY=DAILY_BK_DIR DUMPFILE=emp_backup_%date:~7,2%%date:~4,2%%date:~10,4%_%time:~0,2%%time:~3,2%%time:~6,2%.dmp LOGFILE=emp_backup_logfile%date:~7,2%%date:~4,2%%date:~10,4%_%time:~0,2%%time:~3,2%%time:~6,2%.log
Day (DD) = %date:~7,2%
Month (MM)=%date:~4,2%
Year (YY)=%date:~10,4%
Hour (HH)=%time:~0.2%
Minutes(MM)=%time:~3,2%
Seconds(SS)=%time:~6,2%
E:>IMPDP TABLES=scott.emp DIRECTORY=DAILY_BK_DIR DUMPFILE=emp_dept.DMP LOGFILE=imp_empdept.log or
E:>IMPDP scott/tiger DIRECTORY=DAILY_BK_DIR DUMPFILE=emp_dept.DMP LOGFILE=imp_empdept.log or
E:>IMPDP FULL=Y DIRECTORY=DAILY_BK_DIR DUMPFILE=emp_dept.DMP LOGFILE=imp_empdept.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/guess1408 SCHEMAS=KASB_NHR DIRECTORY=EXP_DUMP DUMPFILE=kasb_nhr_19122012.log LOGFILE=expkbc_hist_19.log
E:>IMPDP system/oracle DIRECTORY=DAILY_BK_DIR DUMPFILE=scott.dmp LOGFILE=impscott.log
E:>IMPDP system/guess1408 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/oracle 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/oracle TABLESPACES=users DIRECTORY=DAILY_BK_DIR DUMPFILE=tbsaxyz.dmp LOGFILE=imp_tbsxyz.log
Database Level:
===============
E:>EXPDP system/manager DIRECTORY=datapump DUMPFILE=fulldb.%U.dmp LOGFILE=fulldb.log parallel=9
expdp system/manager 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: