Context Index Dictionary Cleaning Orphan Records
DRG-10507 - Duplicate Index Name - False Positive 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