How to Diagnose and Fix Unusable Oracle Indexes in Production

1. Fault Phenomenon

The production system reported errors on user queries and OGG data replication stopped; the business experienced widespread latency. Investigation revealed that a critical business table's index had become UNUSABLE, and attempts to rebuild a partition failed with errors.

2. Fault Analysis

Because the local partition index of the table turned UNUSABLE, it needed to be rebuilt.
During the rebuild the temporary tablespace was insufficient, causing some partitions to fail.
After extending the temporary tablespace, the rebuild aborted again with ORA‑08106 because the previous
index‑creation statement terminated abnormally. Online index creation keeps the old index visible and logs DML
operations to a journal table. The abnormal termination left Oracle thinking the index creation was still in progress,
so the procedure dbms_repair.online_index_clean must be run manually to clean the leftover metadata before retrying.

3. Fault Handling

3.1 Clean Index Creation Failure Information

declare
  isClean boolean;
begin
  isClean := FALSE;
  while isClean = FALSE loop
    isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id,
                                               dbms_repair.lock_wait);
    dbms_lock.sleep(2);
  end loop;
exception
  when others then
    RAISE;
end;
/

3.2 Rebuild the Index

alter index EDS_DEFECT_IDX1 rebuild \
  partition EDS_DEFECT_2024 \
  tablespace PROPOSAL_DAT_IDX online parallel 4;

-- Ensure the temporary tablespace has enough space and archive space is sufficient.
-- In extreme cases drop the index and recreate it.

4. Reasons for Index Invalidity

4.1 Ordinary Table Index Invalidity

Manual set to UNUSABLE: ALTER INDEX IND_OBJECT_ID UNUSABLE; Table MOVE or online redefinition makes all indexes UNUSABLE. Example: ALTER TABLE TT MOVE; SQL*Loader loading large data sets; if the loader is killed, the database restarts, or tablespace runs out, the index may not be maintained and become invalid.

4.2 Partition Table Index Invalidity

TRUNCATE or DROP on a partition containing data invalidates the global index, while the local partition index remains valid.

EXCHANGE operation always marks both global and partition indexes UNUSABLE unless the INCLUDING INDEXES clause is used.

SPLIT on a partition that contains data makes both global and partition indexes UNUSABLE; SPLIT on an empty partition does not affect them.

MOVE on a partition invalidates both global and partition indexes.

Manual UNUSABLE: ALTER INDEX IND_OBJECT_ID UNUSABLE;. For partitioned tables, ADD does not affect indexes, but TRUNCATE, DROP, EXCHANGE, and SPLIT do, unless UPDATE GLOBAL INDEXES is specified.

5. Monitoring Invalid Indexes

select index_name name, 'No Partition' partition, 'No Subpartition' Subpartition, status
from all_indexes where status not in ('VALID','USABLE','N/A')
union
select index_name name, partition_name partition, 'No Subpartition' Subpartition, status
from all_ind_partitions where status not in ('VALID','USABLE','N/A')
union
select index_name name, partition_name partition, subpartition_name Subpartition, status
from all_ind_subpartitions where status not in ('VALID','USABLE','N/A');

6. Summary

Oracle indexes accelerate row retrieval, but they require proper maintenance. When indexes become UNUSABLE—whether due to insufficient temporary tablespace, table moves, partition operations, or loader interruptions—DBAs must clean residual metadata with dbms_repair.online_index_clean, ensure adequate tablespace, and rebuild the index. Regular monitoring using the provided queries helps detect invalid indexes early, preventing performance degradation and service outages.