Unusable indexes
From Oracle FAQ
Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as 'unusable' with an ALTER INDEX command. A direct path load against a table or partition will also leave its indexes unusable.
Queries and other operations against a table with unusable indexes will generate errors:
ORA-01502: index ‘string.string’ or partition of such index is in unusable state
Detecting[edit]
The following SQL commands can be used to detect unusable indexes:
Indexes:
SELECT owner, index_name, tablespace_name FROM dba_indexes WHERE status = 'UNUSABLE';
Index partitions:
SELECT index_owner, index_name, partition_name, tablespace_name FROM dba_ind_PARTITIONS WHERE status = 'UNUSABLE';
Index subpartitions:
SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name FROM dba_ind_SUBPARTITIONS WHERE status = 'UNUSABLE';
Fixing[edit]
The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:
Indexes:
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||' ONLINE PARALLEL;' sql_to_rebuild_index FROM dba_indexes WHERE status = 'UNUSABLE';
Index partitions:
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||' ONLINE PARALLEL;' sql_to_rebuild_index FROM dba_ind_partitions WHERE status = 'UNUSABLE';
Index subpartitions:
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild subpartition '||SUBPARTITION_NAME||' TABLESPACE '||tablespace_name ||' ONLINE PARALLEL;' sql_to_rebuild_index FROM dba_ind_subpartitions WHERE status = 'UNUSABLE';
or if you prefer via single PLSQL anonymous block:
set serveroutput on size unlimited BEGIN FOR x IN ( SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE PARALLEL' comm FROM dba_indexes WHERE status = 'UNUSABLE' UNION ALL SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||partition_name||' ONLINE PARALLEL' FROM dba_ind_PARTITIONS WHERE status = 'UNUSABLE' UNION ALL SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD SUBPARTITION '||subpartition_name||' ONLINE PARALLEL' FROM dba_ind_SUBPARTITIONS WHERE status = 'UNUSABLE' ) LOOP dbms_output.put_line(x.comm); EXECUTE immediate x.comm; END LOOP; END; /