[12c] Drop USER 시 ORA-00604,ORA-00942

DB : 12.2.0.1

테스트 서버에서 DROP USER 할 일이 생겼었는데 DROP USER를 하니 아래와 같은 에러가 발생하였습니다.

 

SQL> DROP USER ORG_REORG CASCADE;


ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

 

 

원인은 ILM 관련 sys의 테이블들이 없었고,(왜 없는지는 모르겠습니다..) 해당 테이블들을 잘 수행되는 DB에서 DDL 스크립트를 가져와 재생성 후 다시 DROP USER 시도를 하니 잘 수행되었습니다. 

분석을 위해 세션 10046 trace 를 생성했고 그것을 통해서 분석하였습니다.

 

1.10046 trace 생성

SQL> alter session set tracefile_identifier = 'YSBAE';

Session altered.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> DROP USER ORG_REORG CASCADE;
DROP USER ORG_REORG CASCADE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist


SQL> alter session set events '10046 trace name context off';

Session altered.


-rw-r-----    1 oracle   dba          764965 Sep  1 09:16 dbarac1_ora_8913202_YSBAE.trc
-rw-r-----    1 oracle   dba          143982 Sep  1 09:16 dbarac1_ora_8913202_YSBAE.trm

 

2.trace 파일 분석

분석결과 delete from ilmpolicy$ where policy# in (select policy#    from ilm$ where owner# = :1) 이 구문을 수행할 때 제가 봤던 에러가 발생하였습니다. 해당 DB에서 ilm$와 ilmpolicy$ 테이블을 조회해보았고, 없었습니다. 정상적으로 수행되는 DB에서 조회결과 테이블들이 조회가 되었습니다.

...
PARSE ERROR #4852899400:len=87 dep=1 uid=0 oct=7 lid=0 tim=30996927532292 err=942
delete from ilmpolicy$ where policy# in (select policy#    from ilm$ where owner# = :1)
WAIT #4853677024: nam='enq: IV -  contention' ela= 740 type|mode=1230372869 id1=1280262987 id2=188 obj#=-1 tim=30996927533319
WAIT #4853677024: nam='enq: IV -  contention' ela= 193 type|mode=1230372869 id1=1398361667 id2=188 obj#=-1 tim=30996927533672
EXEC #4853677024:c=460806,e=877676,p=0,cr=8102,cu=17,mis=0,r=0,dep=0,og=1,plh=0,tim=30996927533751
ERROR #4853677024:err=604 tim=30996927533774

...

 

3.문제가 되었던 테이블 재생성

CREATE TABLE SYS.ILM$
(
    POLICY# NUMBER,
    NAME    VARCHAR2(128) NOT NULL,
    OWNER#  NUMBER,
    TS#     NUMBER,
    PTYPE   NUMBER NOT NULL,
    FLAG    NUMBER,
    FLAG2   NUMBER,
    SPARE1  NUMBER,
    SPARE2  NUMBER,
    SPARE3  NUMBER,
    SPARE4  VARCHAR2(4000),
    SPARE5  VARCHAR2(4000),
    SPARE6  TIMESTAMP(6)
)
TABLESPACE SYSAUX
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

CREATE UNIQUE INDEX SYS.I_ILM$
ON SYS.ILM$ (NAME,OWNER#) 
TABLESPACE SYSAUX
STORAGE
(
    INITIAL 64K
    NEXT 1M
);



CREATE TABLE SYS.ILMPOLICY$
(
    POLICY#         NUMBER,
    ACTIONC         VARCHAR2(100),
    CTYPE           NUMBER,
    CLEVEL          NUMBER,
    CINDEX          NUMBER,
    CPREFIX         NUMBER,
    CLEVLOB         NUMBER,
    TIER_TBS        VARCHAR2(128),
    ACTION          NUMBER,
    TYPE            NUMBER,
    CONDITION       NUMBER,
    DAYS            NUMBER,
    SCOPE           NUMBER,
    CUSTFUNC        VARCHAR2(128),
    FLAG            NUMBER,
    FLAG2           NUMBER,
    SPARE1          NUMBER,
    SPARE2          NUMBER,
    SPARE3          NUMBER,
    SPARE4          VARCHAR2(4000),
    SPARE5          VARCHAR2(4000),
    SPARE6          TIMESTAMP(6),
    POL_SUBTYPE     NUMBER,
    ACTIONC_CLOB    CLOB,
    TIER_TO         NUMBER
)
TABLESPACE SYSAUX
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS
LOB (ACTIONC_CLOB) STORE AS SECUREFILE 
(
    STORAGE
    (
        INITIAL 104K
        NEXT 1M
    )
    CHUNK 8192
    PCTVERSION 0
);

CREATE UNIQUE INDEX SYS.I_ILMPOLICY$
ON SYS.ILMPOLICY$ (POLICY#) 
TABLESPACE SYSAUX
STORAGE
(
    INITIAL 64K
    NEXT 1M
);

 

4.재시도

재시도후에도 에러가 발생하여 trace 파일을 한번 더 생성하였습니다.

SQL> alter session set tracefile_identifier = 'YSBAE';

Session altered.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> DROP USER ORG_REORG CASCADE;
DROP USER ORG_REORG CASCADE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist


SQL> alter session set events '10046 trace name context off';

 

5.trace 파일 재확인

delete from ilmobj$ where policy#    in (select policy# from ilm$ where owner# = :1) 구문에서 에러가 발생하였고 ilmobj$테이블의 존재가 없었습니다. 정상적인 DB에서 조회결과 테이블은 조회가 되었고 DDL 스크립트를 복사해 재생성해주었습니다.

PARSE ERROR #4853952280:len=84 dep=1 uid=0 oct=7 lid=0 tim=31002866800047 err=942
delete from ilmobj$ where policy#    in (select policy# from ilm$ where owner# = :1)
WAIT #4851053904: nam='enq: IV -  contention' ela= 788 type|mode=1230372869 id1=1280262987 id2=188 obj#=-1 tim=31002866801071
WAIT #4851053904: nam='enq: IV -  contention' ela= 363 type|mode=1230372869 id1=1398361667 id2=188 obj#=-1 tim=31002866801571
WAIT #4851053904: nam='enq: IV -  contention' ela= 485 type|mode=1230372869 id1=1280262987 id2=188 obj#=-1 tim=31002866802228
WAIT #4851053904: nam='enq: IV -  contention' ela= 304 type|mode=1230372869 id1=1398361667 id2=188 obj#=-1 tim=31002866802819
EXEC #4851053904:c=122281,e=311901,p=0,cr=924,cu=23,mis=0,r=0,dep=0,og=1,plh=0,tim=31002866802900
ERROR #4851053904:err=604 tim=31002866802929

 

6.테이블 재생성

CREATE TABLE SYS.ILMOBJ$
(
    POLICY#         NUMBER NOT NULL,
    OBJ#            NUMBER NOT NULL,
    DATAOBJ#        NUMBER,
    LAST_CHK_TIME   TIMESTAMP(6),
    LAST_EXE_TIME   TIMESTAMP(6),
    OBJ_TYP         NUMBER,
    OBJ_TYP_ORIG    NUMBER,
    POBJN_ORIG      NUMBER,
    FLAG            NUMBER,
    LAST_JOB_STATUS NUMBER,
    N_FAIL          NUMBER DEFAULT 0,
    N_AFT_NOFILT    NUMBER DEFAULT 0,
    SPARE1          NUMBER,
    SPARE2          NUMBER,
    SPARE3          NUMBER,
    SPARE4          VARCHAR2(4000),
    SPARE5          VARCHAR2(4000),
    SPARE6          TIMESTAMP(6)
)
TABLESPACE SYSAUX
STORAGE
(
    INITIAL 64K
    NEXT 1M
)
NOCOMPRESS;

CREATE UNIQUE INDEX SYS.I_ILMOBJ$
ON SYS.ILMOBJ$ (OBJ#,POLICY#) 
TABLESPACE SYSAUX
STORAGE
(
    INITIAL 64K
    NEXT 1M
);

CREATE INDEX SYS.I_ILMOBJ_OBJ$
ON SYS.ILMOBJ$ (OBJ#) 
TABLESPACE SYSAUX
STORAGE
(
    INITIAL 64K
    NEXT 1M
);

CREATE INDEX SYS.I_ILMOBJ_POL$
ON SYS.ILMOBJ$ (POLICY#) 
TABLESPACE SYSAUX
STORAGE
(
    INITIAL 64K
    NEXT 1M
);

7.DROP USER 성공

그 후에 DROP USER 가 성공하였습니다.

DROP USER ORG_REORG CASCADE;

 

DROP USER 시에 에러가 발생하는 case를 검색해보았는데 저와 동일한 원인은 없어서 해결하는데 좀 더 걸리더라구요..

여러 원인이 있는거 같습니다.