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

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms