Oracle Data Pump (expdp, impdp ) 사용법

1. Oracle Directory 생성 
 
     SQL> create directory  temp_dir as '/DATA/EXPORT'; 
     SQL> grant read, write on directory temp_dir  to user_name;
 

 2. expdb 및 impdp 예제
   
    1) Schema full export 후 import (Tablespace가 Source 와 Target 이 동일하게 존재할 경우)
      # expdp system/password directory=temp_dir filesize=10G schemas=scott dumpfile=scott%U.dmp logfile=scott.log 
         PARALLEL=4
       # impdp system/password directory=temp_dir schemas=scott dumpfile=scott%U.dmp  logfile=scott.log 
         PARALLEL=4 TABLE_EXISTS_ACTION = SKIP
        
  ※ filesize=10G : dump 파일의 사이즈를 10G로 제한, 10G 이상 시 다른 dump 파일로 생성됨
  ※ PARALLEL=4 : 4개의 병렬  세션으로 수행되게 함
  ※ TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}
            
   2) 유저의 특정 Table만 export 후 import
       # expdp system/password directory=temp_dir filesize=10G schemas=scott dumpfile=scott%U.dmp logfile=scott.log 
         TABLES=emp PARALLEL=4
       # impdp system/password directory=temp_dir schemas=scott dumpfile=scott%U.dmp  logfile=scott.log 
         PARALLEL=4 TABLE_EXISTS_ACTION=APPEND

   

      ※ TABLES 옵션 : TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995 (파티션)  

 

   3) META Data만 import 하기
      # expdp system/password directory=temp_dir filesize=10G schemas=scott dumpfile=scott%U.dmp logfile=scott.log 
        CONTENT=METADATA_ONLY
       # impdp system/password directory=temp_dir schemas=scott dumpfile=scott%U.dmp  logfile=scott.log 
          CONTENT=METADATA_ONLY TABLE_EXISTS_ACTION=SKIP
     
    4) 다른 계정, 다른 Tablespace 로 변경하여  import 하기
      # expdp system/password directory=temp_dir filesize=10G schemas=scott dumpfile=scott%U.dmp logfile=scott.log 
      # impdp system/password directory=temp_dir schemas=scott dumpfile=scott%U.dmp  logfile=scott.log 
           REAMP_TABLESPACE='SOURCE1_TBS':'TARGET1_TBS','SOURCE2_TBS':'TARGET2_TBS'
           REMAP_SCHEMA=SCOTT:SCOTT2
     
    5) Package, Function, Procedure 만 import 하기
      # expdp system/password directory=temp_dir filesize=10G schemas=scott dumpfile=scott%U.dmp logfile=scott.log 
          include=PACKAGE,FUNCTION,PROCEDURE
      # impdp system/password directory=temp_dir schemas=scott dumpfile=scott%U.dmp  logfile=scott.log 
          include=PACKAGE,FUNCTION,PROCEDURE
 
     ※ INCLUDE 에서 사용할 수 있는 OBJECT_TYPE은 아래 뷰를 이용하여 확인 가능
    DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS.
           
   
   
     6) 특정 Package,Function,Procedure 만 import 하기
      # expdp system/password directory=temp_dir filesize=10G schemas=scott dumpfile=scott%U.dmp logfile=scott.log 
          include=procedure:\"in \(\'ORA\$_SYS_REP_AUTH\'\)\" 
      # impdp system/password directory=temp_dir schemas=scott dumpfile=scott%U.dmp  logfile=scott.log 
          include=procedure:\"in \(\'ORA\$_SYS_REP_AUTH\'\)\"   ==> Unix 환경에서 \ 문자를 모두 넣어 성공적으로 수행됨 

 

        ※ INCLUDE 및 EXCLUDE 옵션에서는 " , ' , ( ) , $ 와 같은 모든 문자에 \(역슬래쉬)를 입력하여야 syntax 오류가 발생하지 않는다.

 

      INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"    ==> 실제 수행시에는 역슬래쉬를 문자를 모두 넣어주어야 함
      INCLUDE=PROCEDURE
      INCLUDE=INDEX:"LIKE 'EMP%'" 

 

    Syntax 예제)     
        Windows:
         D:\> expdp scott/tiger DIRECTORY=temp_dir DUMPFILE=scott.dmp LOGFILE=scott_exp.log

                  EXCLUDE=TABLE:\”IN (’EMP’, ‘DEP’)\” 
         D:\> impdp scott/tiger DIRECTORY=my_dir DUMPFILE=scott.dmp LOGFILE=scott_imp.log

                  EXCLUDE=TABLE:\”IN (’EMP’, ‘DEP’)\”

        Unix:
        % expdp scott/tiger DIRECTORY=temp_dir DUMPFILE=scott.dmp LOGFILE=scott_exp.log

            EXCLUDE=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”
        % impdp scott/tiger DIRECTORY=my_dir DUMPFILE=scott.dmp LOGFILE=scott_imp.log

            EXCLUDE=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”
    
    
 
3. DATA PUMP JOB 모니터링

    select * from USER_DATAPUMP_JOBS;
    select * from DBA_DATAPUMP_SESSIONS;

   impdp system/passwd  attach=job_name ;

   impdp > stop_job immediate
 
   impdp > kill_job
 
 

 

export 옵션 )

         
                CONTENT=ALL logfile=scott.log

                CONTENT=[ ALL | DATA_ONLY | METADATA_ONLY ] 
              
                INCLUDE=TABLE:="BONUS"
                    BONUS 테이블만 포함
                INCLUDE=[TABLE,INDEX,PROCEDIRE,FUNCTION 등 ] 
                EXCLUDE=TABLE:="BONUS"
                    BONUS table은 제외
                EXCLUDE=INDEX:\"='EMP%'\"
                    EMP 테이블의 인덱스 제외
                QUERY=SCOTT.EMP:'where sal>200' 
                   emp.sal 의 값이 200인 값만 추출   
                SAMPLE=scott.emp:20   
                   emp 테이블의 20% 만 추출
 

impdp 옵션)
 
                CONTENT=ALL logfile=scott.log

                CONTENT=[ ALL | DATA_ONLY | METADATA_ONLY ] 
              
                INCLUDE=TABLE:="BONUS"
                    BONUS 테이블만 포함
                INCLUDE=[TABLE,INDEX,PROCEDIRE,FUNCTION 등 ] 
                EXCLUDE=TABLE:="BONUS"
                    BONUS table은 제외
                EXCLUDE=INDEX:\"='EMP%'\"
                    EMP 테이블의 인덱스 제외   
              
                REAMP_TABLESPACE='SOURCE1_TBS':'TARGET1_TBS','SOURCE2_TBS':'TARGET2_TBS'
                REMAP_SCHEMA=SCOTT:SCOTT2

[출처] Oracle Data Pump (expdp, impdp ) 사용법|작성자 꼰티