DRG-10507 – Duplicate Index Name
When doing some regular checks on SYSAUX tablespace occupants, we have detected that CTXSYS.DR$PENDING table was at 2GB size and there were 15 million records despite we have daily periodic context index sync scheduler jobs.
We used below query to inspect unsynced context index records.
| SQL> SELECT pnd_cid, MIN (pnd_timestamp), COUNT (*) | |
| FROM ctxsys.dr$pending | |
| GROUP BY pnd_cid; | |
| pnd_cid MIN (pnd_timestamp) COUNT(*) | |
| —————- —————- ————— | |
| … … … | |
| 2243 15.09.2020 14191928 | |
| … … … |
We searched for the 2243 id numbered context index in the CTXSYS.DR$INDEX table. It looked like a user created context index. Index owner and names are changed as KARTAL.IDXCTX_SOMETABLE_SOMECOLUMN. I searched dba_objects to get creation date of the object. But there was no record named as KARTAL.IDXCTX_SOMETABLE_SOMECOLUMN. That looked strange. This record seemed like an orphan forgotten record.
We tried to create the non-existent context index with below command and it failed with DRG-10507 as below.
| SQL> CREATE INDEX KARTAL.IDXCTX_SOMETABLE_SOMECOLUMN ON KARTAL.T_SOMETABLE | |
| (SOMECOLUMN) | |
| INDEXTYPE IS CTXSYS.CONTEXT; | |
| ERROR at line 1: | |
| ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine | |
| ORA-20000: Oracle Text error: | |
| DRG-50857: oracle error in drixmd.PurgeKGL | |
| ORA-20000: Oracle Text error: | |
| DRG-50857: oracle error in drdmlpo | |
| ORA-20000: Oracle Text error: | |
| DRG-10502: index 2243 does not exist | |
| DRG-10507: duplicate index name: IDXCTX_SOMETABLE_SOMECOLUMN | |
| ORA-06512: at "CTXSYS.DRUE", line 186 | |
| ORA-06512: at "CTXSYS.DRVXMD", line 45 | |
| ORA-06512: at line 1 | |
| ORA-06510: PL/SQL: unhandled user-de | |
| ORA-30576: ConText Option dictionary loading error | |
| ORA-06510: PL/SQL: unhandled user-defined exception | |
| ORA-06512: at "CTXSYS.DRIXMD", line 2664 | |
| ORA-00001: unique constraint (CTXSYS.DRC$IDX_ | |
| DRG-50610: internal error: kglpurge [] | |
| ORA-06512: at "CTXSYS.DRUE", line 186 | |
| ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320 |
10046 Event Tracing
We have set the 10046 event and execute sql statement again. Sql statement failing was an insert to ctxsys.dr$index table. There was a unique index on (idx_owner#, idx_name) columns. I searched MOS for orphan context index records. Doc ID 2532194.1 : Import raises DRG-10507 error on non-existent domain index looked a little bit related with our issue. We have examined ctxsys.dr$index_value, ctxsys.dr$index_object and ctxsys.dr$index tables respectively. There were also some orphan records with 2243 id on these tables.
We cleared all the orphan records with the below commands.
| SQL> Delete from ctxsys.dr$index_value where IXV_IDX_ID=2243; | |
| SQL> Delete from ctxsys.dr$index_object where IXO_IDX_ID=2243; | |
| SQL> Delete from ctxsys.dr$pending where pnd_cid=2243; | |
| SQL> Delete from ctxsys.dr$index where idx_id=2243; |
Hope it helps.
Osman’s DBlog 
Leave your comment