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
