Recently after upgrading the dev database to 12c, I ran utlrp to compile the invalid objects and it failed with the below error
SQL> @utlrp SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual; TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2015-06-30 03:50:27 SQL> DECLARE 2 threads pls_integer := &&1; 3 BEGIN 4 utl_recomp.recomp_parallel(threads); 5 END; 6 / DECLARE * ERROR at line 1: ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], [] ORA-00604: error occurred at recursive SQL level 2 ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated ORA-06512: at "SYS.DBMS_UTILITY", line 1294 ORA-06512: at line 1 SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual; TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2015-06-30 03:52:10
I enabled 10046 trace and Identified the sql for which error occurred.
insert into plscope_identifier$ (signature,symrep,obj#,type#) values (:1,:2,:3,:4) END OF STMT PARSE #140048740346032:c=0,e=270,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=0,tim=20130922661094 BINDS #140048740346032: Bind#0 oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0001 frm=01 csi=178 siz=32 off=0 kxsbbbfp=7f5fa3484968 bln=32 avl=32 flg=09 value="097661E4DCCCB9DD39AA0F5F154C4DF3" Bind#1 oacdty=01 mxl=32(31) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0001 frm=01 csi=178 siz=32 off=0 kxsbbbfp=7f5fa3484998 bln=32 avl=09 flg=09 value="P_SALE_ID" Bind#2 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=1000001 frm=00 csi=00 siz=48 off=0 kxsbbbfp=7f5fa3443fd0 bln=22 avl=04 flg=05 value=166015 Bind#3 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=1000001 frm=00 csi=00 siz=0 off=24 kxsbbbfp=7f5fa3443fe8 bln=22 avl=02 flg=01 value=7
Checked PLSCOPE_IDENTIFIER$ table details
SQL> @table_info.sql Enter value for table_name: PLSCOPE_IDENTIFIER$ old 9: WHERE table_name like UPPER('%&table_name%') new 9: WHERE table_name like UPPER('%PLSCOPE_IDENTIFIER$%') TABLE_NAME OWNER TABLESPACE_NAME NUM_ROWS LAST_ANALYZED AVG_ROW_LEN BLOCKS EMPTY_BLOCKS ------------------------------ ---------- --------------- ---------- ------------------- ----------- ---------- ------------ PLSCOPE_IDENTIFIER$ SYS SYSAUX 5074 06/30/2015 22:02:37 52 65 0 SQL> desc plscope_identifier$ Name Null? Type ------------------ -------- ------------------- SIGNATURE VARCHAR2(32) SYMREP VARCHAR2(128) OBJ# NUMBER TYPE# NUMBER
From the trace, I do have all bind values , so lets check the details
SQL> select owner,object_name,object_type,object_id,data_object_id,status from dba_objects where object_id=166015 or data_object_id=166015; OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID STATUS ---------- ------------------------- ------------------ ---------- -------------- ------- ADAM GET_SALE_STATUS FUNCTION 166015 0 INVALID SQL> select * from plscope_identifier$ where SIGNATURE='097661E4DCCCB9DD39AA0F5F154C4DF3'; SIGNATURE SYMREP OBJ# TYPE# ---------------------------------------- -------------------- ---------- ---------- 097661E4DCCCB9DD39AA0F5F154C4DF3 P_SALE_ID 166015 7
SQL> desc adam.GET_SALE_STATUS ERROR: ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], [] ORA-00604: error occurred at recursive SQL level 1 ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated SQL> alter session set current_schema=ADAM; Session altered. SQL> alter function GET_SALE_STATUS compile; Function altered. SQL> select owner,object_name,object_type,object_id,data_object_id,status from dba_objects where object_id=166015 or data_object_id=166015; OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID STATUS ---------- ------------------------- ------------------ ---------- -------------- ------- ADAM GET_SALE_STATUS FUNCTION 166015 VALID SQL> desc GET_SALE_STATUS FUNCTION GET_SALE_STATUS RETURNS VARCHAR2(30) Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_SALE_ID NUMBER(38) IN
Re-ran the utlrp, and it ran successfully.
SQL> @?/rdbms/admin/utlrp TIMESTAMP --------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2015-07-02 05:57:41 DOC># PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2015-07-02 05:58:03
SQL to identify Invalid Dependent objects
col object_name for a45 col owner for a25 SELECT object_id, object_name, object_type, owner, status FROM dba_objects WHERE object_name IN (SELECT symrep FROM plscope_identifier$ WHERE obj# IN (SELECT obj# FROM plscope_identifier$ MINUS SELECT object_id FROM dba_objects));