Context Index Dictionary Cleaning Orphan Records

DRG-10507 - Duplicate Index Name - False Positive

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.

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. 

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.

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.

Hope it helps.