MARK_FAILED

Use the MARK_FAILED procedure to change the status of an index from LOADING to FAILED.

Under rare circumstances, if CREATE INDEX or ALTER INDEX fails, an index may be left with the status LOADING. When an index is in LOADING status, any attempt to recover using RESUME INDEX is blocked. For this situation, use CTX_ADM.MARK_FAILED to forcibly change the status from LOADING to FAILED so that you can recover the index with RESUME INDEX.

Note: CTX_ADM.MARK_FAILED will mark the index or index partition as FAILED, even if they are not marked as LOADING.

You must log on as CTXSYS to run CTX_ADM.MARK_FAILED.

WARNING: Use CTX_ADM.MARK_FAILED with caution. It should only be used as a last resort and only when no other session is touching the index. Normally, CTX_ADM.MARK_FAILED does not succeed if another session is actively building the index with CREATE or ALTER INDEX. However, index creation or alteration may include windows of time during which CTX_ADM.MARK_FAILED can succeed, marking the index as failed even as it is being built by another session.

Note: The background processses used to sync the index for automatic-sync indexes are considered as different sessions and CTX_ADMIN.MARK_FAILED will not succeed in such scenarios. RunCTX_ADM.MARK_FAILED only when there are no active background processes.

CTX_ADM.MARK_FAILED works with local partitioned indexes. However, it changes the status of all partitions to FAILED. Therefore, you should rebuild all index partitions with ALTER INDEX REBUILD PARTITION PARAMETERS ('RESUME') after using CTX_ADM.MARK_FAILED. If you run ALTER INDEX PARAMETER ('RESUME') after this operation, then Oracle resets the index partition status to valid. Oracle does not rebuild the index partitions that were successfully built before the MARK_FAILED operation.

Syntax

CTX_ADM.MARK_FAILED(
  owner_name     in    VARCHAR2,
  index_name     in    VARCHAR2);

owner_name

The name of the owner of the index whose status is to be changed.

index_name

The name of the index whose status is to be changed.

Note: The index_name must not be prefixed by the schema or the owner name.

Example

begin
   CTX_ADM.MARK_FAILED('owner_1', 'index_1');
end;