oracle expdp와 impdp

1. 기본

   1) dir 생성 (11g이상)

   create directory export as '/home/export'

 

   2) data dump

   expdp schemas=hr directory=export dumpfile=exphr.dmp logfile=exphr.log exclude=table_data

 

   3) data import

   impdp directory=import dumpfile=exphr.dmp logfile=impdphr.log

 

 

2. 응용

 

  1) 테이블 및 병렬

     expdp userid=test directory=exp_dir dumpfile=t_%u.dmp  logfile=t_log tables=a,b,c parallel=4

 

  2) scn 번호

     select current_scn from v$database;

     expdp userid=test directory=exp_dir dumpfile=t_%u.dmp  logfile=t_log tables=a,b,c parallel=4 flashback_scn=xxxxx

   

  3) 파티션 

     expdp userid=test directory=exp_dir dumpfile=t_%u.dmp  logfile=t_log tables=a:p1,b:p1,c:p1 parallel=4 flashback_scn=xxxxx

 

  4) include 

 

     expdp parfile=test.par

 

     [test.par]

     directory=exp_dir

     dumpfile=test.dmp

     schemas=test1,test2,test3

     content=all

     logfile=test_exp.log

     include=TABLE:"IN (

     'T1',

     'T2',

     'T3' 

     )"

 

   5) data only

     expdp userid=test directory=exp_dir dumpfile=t_%u.dmp  logfile=t_log tables=a,b,c parallel=4 flashback_scn=xxxxx content=data_only

 

   6) ddl만 추출

     impdp userid=test directory=exp_dir dumpfile=t_%u.dmp  logfile=t_log sqlfile=testddl.sql

 

   7) contraint, 혹은 foreign 키, user, trigger 제외하고 import

    impdp userid=test directory=exp_dir dumpfile=t_%u.dmp  logfile=t_log exclude=user,ref_constraint,trigger,constraint

 

   8) foreign 키만 import

     impdp userid=test directory=exp_dir dumpfile=t_%u.dmp  logfile=t_log include=ref_constraint