[오라클] impdp 타겟에 존재하는 스키마 및 오브젝트를 처리하는 파라미터

-- target에 이미 존재하는 스키마를 import하기
-- table_exists_action에서 skipp관련 에러 발생

ORA-31684 에러는 받을 스키마가 이미 존재해서 발생하는 에러라 무시하고 진행해도 무방하다. 가장 하단의 EXCLUDE 파라미터를 이용해 없앨 수 있다. 

impdp hr5 directory=dpump_dir2 dumpfile=dpump_dir1:exp_hr_schema_20220216_01.dmp remap_schema=hr4:hr5 logfile=imp_hr_schema_20220216_02.log

Import: Release 19.0.0.0.0 - Production on Wed Feb 16 11:42:54 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "HR5"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR5"."SYS_IMPORT_FULL_01":  hr5/pw directory=dpump_dir2 dumpfile=dpump_dir1:exp_hr_schema_20220216_01.dmp remap_schema=hr4:hr5 logfile=imp_hr_schema_20220216_02.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HR5" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "HR5"."EMPLOYEES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "HR5"."DEPARTMENTS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "HR5"."COUNTRIES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "HR5"."LOCATIONS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "HR5"."JOBS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "HR5"."JOB_HISTORY" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "HR5"."REGIONS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "HR5"."SYS_IMPORT_FULL_01" completed with 8 error(s) at Wed Feb 16 11:42:59 2022 elapsed 0 00:00:02

 

해결 방법

아래처럼 table_exists_action, exclude를 사용해 존재하는 테이블 제외 

$ impdp hr5 directory=dpump_dir2 dumpfile=dpump_dir1:exp_hr_schema_20220216_01.dmp table_exists_action=skip remap_schema=hr4:hr5 exclude=USER:\"=\'HR4\'\" logfile=imp_hr_schema_20220216_02.log