ORA-00955: name is already used by an existing object

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

에러 : ORA-00955: name is already used by an existing object

기존에 expdp를 실행했다가 강제로 중지한 뒤 다시 expdp 실행시 정상적으로 실행되지 않는 문제

$ expdp system/oracle job_name=expdp_20230606_IMSIBTL directory=EXPDP_20230606_1 logfile=expdp_20230606_IMSIBTL.log parallel=4 
tables=IMSI.IMSITBL dumpfile=expdp_20230606_IMSIBTL%U.dmp compression=all cluster=N status=60
 
Export: Release 11.2.0.4.0 - Production on Tue Jun 6 20:17:16 2023
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.expdp_20230606_IMSIBTL"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-00955: name is already used by an existing object

 

 

기존 expdp job으로 접속 시도

$ expdp attach=expdp_IMSITBL_20230606
 
Export: Release 11.2.0.4.0 - Production on Tue Jun 6 20:13:14 2023
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
Username: system
Password:
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 405
ORA-31638: cannot attach to job EXPDP_IMSITBL_20230606 for user SYSTEM
ORA-31632: master table "SYSTEM.EXPDP_IMSITBL_20230606" not found, invalid, or inaccessible
ORA-00942: table or view does not exist

job이 없다고 나옴

 

 

해결 방법 : dba_datapump_jobs 조회 후 해당 오브젝트 삭제

SQL>
select owner_name, job_name, operation, job_mode,
       state, attached_sessions
from   dba_datapump_jobs
where  job_name not like 'BIN$%'
order  by 1, 2;
 
OWNER_NAME    JOB_NAME    OPERATION    JOB_MODE    STATE    ATTACHED_SESSIONS    
--------   -----------         -------------- -------------- ----------  -----------------
SYSTEM    expdp_20230606_IMSIBTL    EXPORT        SCHEMA        NOT RUNNING     10

제거하려는 expdp job이 STATE 가 NOT RUNNING 임

 

 

이 경우 해당 테이블 정보 확인

SQL>
select o.status, o.object_id, o.object_type,
       o.owner||'.'||object_name "OWNER.OBJECT"
from   dba_objects o, dba_datapump_jobs j
where  o.owner=j.owner_name and
       o.object_name=j.job_name and
       j.job_name not like 'BIN$%'
order  by 4, 2;
 
STATUS    OBJECT_ID    OBJECT_TYPE    OWNER.OBJECT
------ ---------- ------------  ---------------
VALID    457252    TABLE    SYSTEM.expdp_20230606_IMSIBTL

 

 

해당 임시 테이블(job) 삭제

(""를 안넣는 경우 없는 테이블이라 나오는 경우도 있으니 테이블 이름에 ""사용해서 drop)

SQL> drop table SYSTEM."expdp_20230606_IMSIBTL";
 
Table dropped.

 

 

다시 expdp 실행

$ expdp system/oracle job_name=expdp_20230606_IMSIBTL directory=EXPDP_20230606_1 logfile=expdp_20230606_IMSIBTL.log parallel=4 
tables=IMSI.IMSITBL dumpfile=expdp_20230606_IMSIBTL%U.dmp compression=all cluster=N status=60
(정상)

정상적으로 실행됨

 

 

원인 : 정상적으로 종료되지 못한 expdp job이 남아있어서 발생한 문제

정상적으로 종료되지 못한 expdp job이 남아있어서 발생한 문제

비정상 expdp job을 제거후 정상적으로 동작함

 

 

참조 : 556425.1, 336014.1