ORA-00600 [kqlidchg0] | ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated

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));

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s