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

OS환경 : Oracle Linux 7.4 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

에러 : ORA-39151: Table "IMSI"."PTABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

impdp 시 발생하는 에러

$ impdp system/oracle directory=partpump dumpfile=part_dump.dmp logfile=part_dump_impdp.log tables=IMSI.PTABLE:P202105
 
Import: Release 19.0.0.0.0 - Production on Wed Jan 10 16:49:45 2024
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/****** directory=partpump dumpfile=part_dump.dmp logfile=part_dump_impdp.log tables=IMSI.PTABLE:P202105
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "IMSI"."PTABLE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Wed Jan 10 16:49:48 2024 elapsed 0 00:00:03

 

 

해결 방법 : 동일한 테이블이 이미 존재하는 경우 table_exists_action 옵션 사용

impdp 할때 동일한 테이블이 이미 존재하는 경우 table_exists_action 옵션을 사용해야함

table_exists_action 은 4가지 옵션이 존재함

SKIP : 여러 테이블을 import 하는 경우 해당 테이블을 skip하고 다음 테이블 import 함 (CONTENT=DATA_ONLY를 사용한 경우 이 옵션은 무시됨)(기본값)
APPEND : 기존 행을 놔둔채로 import 함
TRUNCATE : 기존 행을 삭제한 뒤 import 함
REPLACE : 기존 테이블을 삭제한 뒤 테이블을 재생성하고 import 함 (CONTENT=DATA_ONLY를 사용한 경우 이 옵션은 무시됨)

 

table_exists_action 옵션 넣어서 impdp 재실행(나의 경우 append를 사용함)

$ impdp system/oracle directory=partpump dumpfile=part_dump.dmp logfile=part_dump_impdp.log tables=IMSI.PTABLE:P202105 table_exists_action=append
 
Import: Release 19.0.0.0.0 - Production on Wed Jan 10 16:58:16 2024
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******  directory=partpump dumpfile=part_dump.dmp logfile=part_dump_impdp.log tables=IMSI.PTABLE:P202105 table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "IMSI"."PTABLE" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "IMSI"."PTABLE":"P202105"                   117.3 MB 7283184 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 10 16:58:24 2024 elapsed 0 00:00:07

정상적으로 impdp 가 동작함

 

 

원인 : impdp 할 대상 테이블이 이미 존재할때 발생하는 문제

impdp 할 대상 테이블이 이미 존재할때 발생하는 문제로 impdp시 어떻게 대처할지 table_exists_action 옵션을 미리 지정해줘야함

 

 

참조 : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/datapump-import-utility.html#GUID-C9664F8C-19C5-4177-AC20-5682AEABA07F