본문 바로가기

DBMS/ORACLE

[ORACLE] impdp 옵션

반응형

개요

구성환경

 

1. REMAP_SCHEMA 옵션

예) hoya 스키마의 테이블을 hr 스키마로 임포트

shell> impdp system@orclpdb remap_schema=hoya:hr directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp nologfile=y

Import: Release 19.0.0.0.0 - Production on 월 2월 3 10:09:58 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
비밀번호:

접속 대상: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
마스터 테이블 "SYSTEM"."SYS_IMPORT_FULL_01"이(가) 성공적으로 로드됨/로드 취소됨
"SYSTEM"."SYS_IMPORT_FULL_01" 시작 중: system/********@orclpdb remap_schema=hoya:hr directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp nologfile=y
객체 유형 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 처리 중
객체 유형 SCHEMA_EXPORT/TABLE/TABLE 처리 중
객체 유형 SCHEMA_EXPORT/TABLE/TABLE_DATA 처리 중
. . "HR"."TEST1"                                5.101 KB       5행이 임포트됨
. . "HR"."TEST2"                                5.101 KB       5행이 임포트됨
객체 유형 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 처리 중
객체 유형 SCHEMA_EXPORT/STATISTICS/MARKER 처리 중
"SYSTEM"."SYS_IMPORT_FULL_01" 작업이 월 2월 3 10:10:19 2020 elapsed 0 00:00:17에서 성공적으로 완료됨

shell>

 

2. REMPA_TABLE 옵션

Syntax)

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename

예) hoya 스키마의 test1 테이블 임포트시 test1_bak 이름으로 변경하여 임포트

shell> impdp hoya@orclpdb directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp TABLES=hoya.test1 REMAP_TABLE=hoya.test1:test1_bak nologfile=y

Import: Release 19.0.0.0.0 - Production on 월 2월 3 10:44:33 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
비밀번호:

접속 대상: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
마스터 테이블 "HOYA"."SYS_IMPORT_TABLE_01"이(가) 성공적으로 로드됨/로드 취소됨
"HOYA"."SYS_IMPORT_TABLE_01" 시작 중: hoya/********@orclpdb directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp TABLES=hoya.test1 REMAP_TABLE=hoya.test1:test1_bak nologfile=y
객체 유형 SCHEMA_EXPORT/TABLE/TABLE 처리 중
객체 유형 SCHEMA_EXPORT/TABLE/TABLE_DATA 처리 중
. . "HOYA"."TEST1_BAK"                          5.101 KB       5행이 임포트됨
객체 유형 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 처리 중
객체 유형 SCHEMA_EXPORT/STATISTICS/MARKER 처리 중
"HOYA"."SYS_IMPORT_TABLE_01" 작업이 월 2월 3 10:44:53 2020 elapsed 0 00:00:15에서 성공적으로 완료됨

shell>

 

3. REMAP_TABLESPACE 옵션

예) 임포트시 users 테이블스페이스의 오브젝트를 users02 테이블스페이스로 임포트 

shell> impdp system@orclpdb  directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp REMAP_TABLESPACE=users:users02 nologfile=y table_exists_action=REPLACE

Import: Release 19.0.0.0.0 - Production on 월 2월 3 12:40:21 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
비밀번호:

접속 대상: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
마스터 테이블 "SYSTEM"."SYS_IMPORT_FULL_01"이(가) 성공적으로 로드됨/로드 취소됨
"SYSTEM"."SYS_IMPORT_FULL_01" 시작 중: system/********@orclpdb directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp REMAP_TABLESPACE=users:users02 nologfile=y table_exists_action=REPLACE
객체 유형 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 처리 중
객체 유형 SCHEMA_EXPORT/TABLE/TABLE 처리 중
객체 유형 SCHEMA_EXPORT/TABLE/TABLE_DATA 처리 중
. . "HOYA"."TEST1"                              5.101 KB       5행이 임포트됨
. . "HOYA"."TEST2"                              5.101 KB       5행이 임포트됨
객체 유형 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 처리 중
객체 유형 SCHEMA_EXPORT/STATISTICS/MARKER 처리 중
"SYSTEM"."SYS_IMPORT_FULL_01" 작업이 월 2월 3 12:40:38 2020 elapsed 0 00:00:14에서 성공적으로 완료됨

shell>

SQL> select owner,table_name,tablespace_name from dba_tables where tablespace_name like 'USER%' ;

OWNER      TABLE_NAME           TABLESPACE_NAME
---------- -------------------- ------------------------------
HOYA       TEST1                USERS02
HOYA       TEST2                USERS02

SQL>

)

shell> impdp system@orclpdb  directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp REMAP_SCHEMA=hoya:hr  REMAP_TABLESPACE=users:users02 nologfile=y 

Import: Release 19.0.0.0.0 - Production on 월 2월 3 12:29:03 2020 
Version 19.3.0.0.0 

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. 
비밀번호: 

접속 대상: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 
마스터 테이블 "SYSTEM"."SYS_IMPORT_FULL_01"이(가) 성공적으로 로드됨/로드 취소됨 
"SYSTEM"."SYS_IMPORT_FULL_01" 시작 중: system/********@orclpdb directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp REMAP_SCHEMA=hoya:hr REMAP_TABLESPACE=users:users02 nologfile=y 
객체 유형 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 처리 중 
객체 유형 SCHEMA_EXPORT/TABLE/TABLE 처리 중 
객체 유형 SCHEMA_EXPORT/TABLE/TABLE_DATA 처리 중 
. . "HR"."TEST1"                                5.101 KB       5행이 임포트됨 
. . "HR"."TEST2"                                5.101 KB       5행이 임포트됨 
객체 유형 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 처리 중 
객체 유형 SCHEMA_EXPORT/STATISTICS/MARKER 처리 중 
"SYSTEM"."SYS_IMPORT_FULL_01" 작업이 월 2월 3 12:29:20 2020 elapsed 0 00:00:13에서 성공적으로 완료됨 

shell>

SQL> select owner,table_name,tablespace_name from dba_tables where tablespace_name like 'USER%' ; 

OWNER      TABLE_NAME           TABLESPACE_NAME 
---------- -------------------- ------------------------------ 
HOYA       TEST1                USERS 
HOYA       TEST2                USERS 
HR         TEST1                USERS02 
HR         TEST2                USERS02 

SQL>

 

4. TABLE_EXISTS_ACTION 옵션

Syntax)

TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}

- REPLACE는 기존 테이블을 삭제 한 다음 소스에서 작성하고로드합니다. CONTENT 매개 변수가 DATA_ONLY로 설정된 경우 유효한 옵션이 아닙니다.

- TRUNCATE는 기존 행을 삭제 한 다음 소스에서 행을로드합니다.

- APPEND는 소스에서 행을로드하고 기존 행을 변경하지 않은 채로 둡니다.

- SKIP은 테이블을 그대로두고 다음 오브젝트로 넘어갑니다. CONTENT 매개 변수가 DATA_ONLY로 설정된 경우 유효한 옵션이 아닙니다.

예) TRUNCATE

shell> impdp system@orclpdb remap_schema=hoya:hr directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp nologfile=y TABLE_EXISTS_ACTION=TRUNCATE

Import: Release 19.0.0.0.0 - Production on 월 2월 3 10:27:25 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
비밀번호:

접속 대상: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
마스터 테이블 "SYSTEM"."SYS_IMPORT_FULL_01"이(가) 성공적으로 로드됨/로드 취소됨
"SYSTEM"."SYS_IMPORT_FULL_01" 시작 중: system/********@orclpdb remap_schema=hoya:hr directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp nologfile=y TABLE_EXISTS_ACTION=TRUNCATE
객체 유형 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 처리 중
객체 유형 SCHEMA_EXPORT/TABLE/TABLE 처리 중
"HR"."TEST1" 테이블이 존재하며 잘렸습니다. truncate의 table_exists_action으로 인해 데이터는 로드되지만 모든 종속 메타데이터를 건너 뜁니다.
"HR"."TEST2" 테이블이 존재하며 잘렸습니다. truncate의 table_exists_action으로 인해 데이터는 로드되지만 모든 종속 메타데이터를 건너 뜁니다.
객체 유형 SCHEMA_EXPORT/TABLE/TABLE_DATA 처리 중
. . "HR"."TEST1"                                5.101 KB       5행이 임포트됨
. . "HR"."TEST2"                                5.101 KB       5행이 임포트됨
객체 유형 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 처리 중
객체 유형 SCHEMA_EXPORT/STATISTICS/MARKER 처리 중
"SYSTEM"."SYS_IMPORT_FULL_01" 작업이 월 2월 3 10:27:34 2020 elapsed 0 00:00:05에서 성공적으로 완료됨

shell>

예) REPLACE

shell> impdp system@orclpdb remap_schema=hoya:hr directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp nologfile=y TABLE_EXISTS_ACTION=REPLACE

Import: Release 19.0.0.0.0 - Production on 월 2월 3 10:26:26 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
비밀번호:

접속 대상: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
마스터 테이블 "SYSTEM"."SYS_IMPORT_FULL_01"이(가) 성공적으로 로드됨/로드 취소됨
"SYSTEM"."SYS_IMPORT_FULL_01" 시작 중: system/********@orclpdb remap_schema=hoya:hr directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp nologfile=y TABLE_EXISTS_ACTION=REPLACE
객체 유형 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 처리 중
객체 유형 SCHEMA_EXPORT/TABLE/TABLE 처리 중
객체 유형 SCHEMA_EXPORT/TABLE/TABLE_DATA 처리 중
. . "HR"."TEST1"                                5.101 KB       5행이 임포트됨
. . "HR"."TEST2"                                5.101 KB       5행이 임포트됨
객체 유형 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 처리 중
객체 유형 SCHEMA_EXPORT/STATISTICS/MARKER 처리 중
"SYSTEM"."SYS_IMPORT_FULL_01" 작업이 월 2월 3 10:26:49 2020 elapsed 0 00:00:18에서 성공적으로 완료됨

shell>

예) SKIP

shell> impdp system@orclpdb remap_schema=hoya:hr directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp nologfile=y TABLE_EXISTS_ACTION=SKIP

Import: Release 19.0.0.0.0 - Production on 월 2월 3 10:33:44 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
비밀번호:

접속 대상: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
마스터 테이블 "SYSTEM"."SYS_IMPORT_FULL_01"이(가) 성공적으로 로드됨/로드 취소됨
"SYSTEM"."SYS_IMPORT_FULL_01" 시작 중: system/********@orclpdb remap_schema=hoya:hr directory=DATA_PUMP_DIR2 dumpfile=schema_hoya.dmp nologfile=y TABLE_EXISTS_ACTION=SKIP
객체 유형 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 처리 중
객체 유형 SCHEMA_EXPORT/TABLE/TABLE 처리 중
"HR"."TEST1" 테이블이 존재합니다. skip의 table_exists_action으로 인해 모든 종속 메타데이터와 데이터를 건너 뜁니다.
"HR"."TEST2" 테이블이 존재합니다. skip의 table_exists_action으로 인해 모든 종속 메타데이터와 데이터를 건너 뜁니다.
객체 유형 SCHEMA_EXPORT/TABLE/TABLE_DATA 처리 중
객체 유형 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 처리 중
객체 유형 SCHEMA_EXPORT/STATISTICS/MARKER 처리 중
"SYSTEM"."SYS_IMPORT_FULL_01" 작업이 월 2월 3 10:33:51 2020 elapsed 0 00:00:03에서 성공적으로 완료됨

shell>

 

5. NETWORK_LINK 옵션

 * source_database 사용자에게 exp_full_database 권한, target database 사용자에게는 imp_full_database 권한 부여

 - Source database

SQL> grant exp_full_database to sales ; 

 - Target Database

SQL> grant imp_full_database to hoya ;

- DB LINK 생성(target database)

SQL> create database link DB_LINK_SALES connect to sales identified  by password using 'pdb1' ;

 

예1) Table 모드

 - source database내 sales 스키마의 table1 테이블을 taget database의 hoya 스키마로 임포트

shell> impdp hoya@orclpdb  directory=DATA_PUMP_DIR2 network_link=DB_LINK_SALES tables=table1 remap_schema=sales:hoya

Import: Release 19.0.0.0.0 - Production on 월 2월 3 21:38:11 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
비밀번호:

접속 대상: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
"HOYA"."SYS_IMPORT_TABLE_01" 시작 중: hoya/********@orclpdb directory=DATA_PUMP_DIR2 network_link=DB_LINK_SALES tables=table1 remap_schema=sales:hoya
BLOCKS 메소드를 사용하여 예측 진행 중...
객체 유형 TABLE_EXPORT/TABLE/TABLE_DATA 처리 중
BLOCKS 메소드를 사용한 총 예측: 64 KB
객체 유형 TABLE_EXPORT/TABLE/TABLE 처리 중
. . "HOYA"."TABLE1"                                  1행이 임포트됨
객체 유형 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 처리 중
객체 유형 TABLE_EXPORT/TABLE/STATISTICS/MARKER 처리 중
"HOYA"."SYS_IMPORT_TABLE_01" 작업이 월 2월 3 21:38:27 2020 elapsed 0 00:00:13에서 성공적으로 완료됨

shell>

예2) Schema 모드

 - source database의 sales 스키마 오브젝트를 taget database의 hoya 스키마로 임포트

shell> impdp hoya@orclpdb  directory=DATA_PUMP_DIR2 network_link=DB_LINK_SALES  remap_schema=sales:hoya table_exists_action=replace            
Import: Release 19.0.0.0.0 - Production on 월 2월 3 21:41:15 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
비밀번호:

접속 대상: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
"HOYA"."SYS_IMPORT_SCHEMA_01" 시작 중: hoya/********@orclpdb directory=DATA_PUMP_DIR2 network_link=DB_LINK_SALES remap_schema=sales:hoya table_exists_action=replace
BLOCKS 메소드를 사용하여 예측 진행 중...
객체 유형 SCHEMA_EXPORT/TABLE/TABLE_DATA 처리 중
BLOCKS 메소드를 사용한 총 예측: 128 KB
객체 유형 SCHEMA_EXPORT/USER 처리 중
ORA-31684: 객체 유형 USER:"HOYA"이(가) 존재함

객체 유형 SCHEMA_EXPORT/ROLE_GRANT 처리 중
객체 유형 SCHEMA_EXPORT/DEFAULT_ROLE 처리 중
객체 유형 SCHEMA_EXPORT/TABLESPACE_QUOTA 처리 중
객체 유형 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 처리 중
객체 유형 SCHEMA_EXPORT/TABLE/TABLE 처리 중
. . "HOYA"."TABLE1"                                  1행이 임포트됨
. . "HOYA"."TABLE2"                                  3행이 임포트됨
객체 유형 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 처리 중
객체 유형 SCHEMA_EXPORT/STATISTICS/MARKER 처리 중
"HOYA"."SYS_IMPORT_SCHEMA_01" 작업이 1 오류와 함께 월 2월 3 21:41:44 2020 elapsed 0 00:00:26에서 완료됨

shell>

 

 

반응형

'DBMS > ORACLE' 카테고리의 다른 글

alert log 파일 위치  (0) 2020.07.19
[ORACLE] DB Link 관리  (0) 2020.02.03
[ORACLE] expdp/impdp 명령어  (0) 2020.01.31
[ORACLE] RMAN을 이용하여 PDB 테이블 복구  (0) 2020.01.30
[ORACLE] SCN을 Date로 변환 / Date를 SCN 변환  (0) 2020.01.30