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

When users performing the transactions on tables where DOMAIN,SPATIAL indexes are part of it they may receive below error and unable to proceed.

SQL> DELETE FROM USER_URL where CXT_ID=8484884;
ERROR at line 1: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

The main reason for this error is user might have copied the table/index using export/import or different method but the CREATE INDEX and failed.

Find out what caused the failure of CREATE INDEX. In many cases not having enough space on the Tablespace where you are creating the index is the culprit.

SQL> SELECT INDEX_NAME, TABLE_NAME STATUS,DOMIDX_STATUS,DOMIDX_OPSTATUS FROM DBA_INDEXES WHERE DOMIDX_OPSTATUS='FAILED';

INDEX_NAME     TABLE_NAME STATUS   DOMIDX_STATUS  DOMIDX_OPSTATUS
------------------ --------------- ---------- --------------------- -------------------------  
USER_URL_IDX  USER_URL      VALID    VALID                    FAILED


When verified I found that schema refreshed recently, and index not created successfully.

ORA-31693: Table data object "FIN"."USER_URL" failed to load/unload and is being skipped due to error:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

If you need workaround to proceed with transactions immediately, then drop the index.
The permanent fix will be drop and recreate index successfully.

To avoid this type of issues, make sure you perform following checks.

1. Before refresh verify the tablespace or file systems has enough space and add required space
2. After import check the data pump or import log file and fix all failures
3. Run utlrp.sql to recompile all invalid objects.

Thanks & Regards

https://oracleracexpert.com