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. 

	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
	... ... ...

view raw unsynced_context_indexes.sql hosted with ❤ by GitHub

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

view raw create_contextindex.sql hosted with ❤ by GitHub

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;

view raw clear_context_index_dict.sql hosted with ❤ by GitHub

Hope it helps.