[ORACLE] expdp / impdp
*. expdp
---------------
1. 디렉토리 조회
SQL> SELECT * FROM dba_directories;
2. 디렉토리 추가
SQL> DROP DIRECTORY dpump_dir2; -- 기존 디렉토리 dpump_dir2 drop
SQL> CREATE DIRECTORY dpump_dir2 as '/backup/dpump'; -- /backup/dpump 에 대한 디렉토리 dpump_dir2 생성
3. 디렉토리에 대한 권한 설정
SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir2 to 사용자;
4. expdp
# expdp system/1239 DIRECTORY=dpump_dir2 schemas=MESS_ADM DUMPFILE=MESS_ADM_20081223.dmp logfile=MESS_ADM_20081223.log
# expdp SYSTEM/1239 DIRECTORY=DPUMP_DIR2 DUMPFILE=expdp_alldata_0106.dmp LOGFILE=expdp_alldata_0106.log PARFILE=expdp.par CONTENT=DATA_ONLY
# expdp system/1239 DIRECTORㅣY=dpump_dir2 tables=MESS_ADM.TB_ABC110 DUMPFILE=tb_ABC110_20100601.dmp logfile=tb_ABC110_20100601.log CONTENT=DATA_ONLY
* expdp(또는 impdp) 작업 진행 중 Control+C를 누르면 export> 프롬프트(또는 import> 프롬프트) 상태가 됨.
Control+C 했다고 해서 작업이 중단되지는 않고, interactive mode로 변경되어 expdp(또는 impdp) 작업을
모니터링하고 제어 가능
[interactive mode에서 사용할 수 있는 명령어]
- STATUS : 현재 작업진행정도 확인 가능
- CONTINUE_CLIENT : 다시 원래 모드로 돌아감
- KILL_JOB
- STOP_JOB
- 나머지 명령어는 HELP 참고
------------------------
*. impdp
------------------------
1. 디렉토리 조회
SQL> SELECT * FROM dba_directories;
2. 디렉토리 추가
SQL> DROP DIRECTORY dpump_dir2; -- 기존 디렉토리 dpump_dir2 drop
SQL> CREATE DIRECTORY dpump_dir2 as '/backup/dpump'; -- /backup/dpump 에 대한 디렉토리 dpump_dir2 생성
3. 디렉토리에 대한 권한 설정
SQL> GRANT READ, WRITE ON DIRECTORY dpump_dir2 to 사용자;
4. impdp
#impdp system/1239 dumpfile=PT_ABC110_02.dmp directory=dpump_dir2 job_name=job_impdp2 logfile=impdp_PT_ABC110_02.log TABLES=MESS_ADM.TB_ABC110 parallel=4 TABLE_EXISTS_ACTION=APPEND
[TABLE_EXISTS_ACTION 옵션]
같은 이름의 테이블이 존재할 때 SKIP / APPEND / TRUNCATE / REPLACE
[샘플]
*. expdp(파티션 테이블)
------------------------
# expdp system/1239 DIRECTORY=dpump_dir2 tables=MESS_ADM.TB_ABC110:PT_ABC110_01 DUMPFILE=PT_ABC110_01.dmp logfile=PT_ABC110_01.log CONTENT=DATA_ONLY
# expdp system/1239 DIRECTORY=dpump_dir2 tables=MESS_ADM.TB_ABC110:PT_ABC110_02 DUMPFILE=PT_ABC110_02.dmp logfile=PT_ABC110_02.log CONTENT=DATA_ONLY
# expdp system/1239 DIRECTORY=dpump_dir2 tables=MESS_ADM.TB_ABC110:PT_ABC110_03 DUMPFILE=PT_ABC110_03.dmp logfile=PT_ABC110_03.log CONTENT=DATA_ONLY
*. impdp(파티션 테이블)
------------------------
# impdp system/1239 dumpfile=PT_ABC110_02.dmp directory=dpump_dir2 job_name=job_impdp2 logfile=impdp_PT_ABC110_02.log TABLES=MESS_ADM.TB_ABC110 parallel=4 TABLE_EXISTS_ACTION=APPEND
Import: Release 10.2.0.2.0 - 64bit Production on Wednesday, 02 June, 2010 0:31:37
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
Master table "SYSTEM"."JOB_IMPDP2" successfully loaded/unloaded
Starting "SYSTEM"."JOB_IMPDP2": system/******** dumpfile=PT_ABC110_02.dmp directory=dpump_dir2 job_name=job_impdp2
logfile=impdp_PT_ABC110_02.log TABLES=MESS_ADM.TB_ABC110 parallel=4 TABLE_EXISTS_ACTION=APPEND
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MESS_ADM"."TB_ABC110":"PT_ABC110_02" 746.4 MB 18653423 rows
Job "SYSTEM"."JOB_IMPDP2" successfully completed at 00:32:53
[출처] 이관 - expdp/impdp 명령어|작성자 smileDBA