Inserting into Table with XMLINDEX Causes Intermittent ORA-29861
(Doc ID 1404559.1)
Last updated on JANUARY 30, 2022
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.
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 |