My Oracle Support Banner

Inserting into Table with XMLINDEX Causes Intermittent ORA-29861 (Doc ID 1404559.1)

Last updated on MARCH 28, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 22-Oct-2013***

Symptoms

Inserting into table which has an XMLIndex sometimes causes below error-

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE.

This has been seen to happen when there is a Gather Stats job running at the same time when the DML is happening on this table.

At the time of the error index will be in INPROGRS state, e.g. -

SQL> select index_name, index_type, ityp_name, status from dba_indexes where status!='VALID';
.
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
ITYP_NAME STATUS
------------------------------ --------
ORD_ORDER_CUST_DATA_VIRT_IDX FUNCTION-BASED DOMAIN
XMLINDEX INPROGRS

Rebuilding index does not fix the index when it reaches this state. Only solution in this scenario is to manually recreate it.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.