Unusable indexes

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 agains a table with unuable indexes will generate errors:

ORA-01502: index ‘string.string’ or partition of such index is in unusable state

Detecting

The following SQL comands can be used to detect unuable 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';

Fixing

The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:

Indexes:

SELECT 'alter index '||index_name||' rebuild tablespace |tablespace_name ||';'
FROM   dba_indexes
WHERE  status = 'UNUSABLE';

Index partitions:

SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';'
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE';