Index Status Types in DBA_INDEXES

SELECT DISTINCT STATUS FROM DBA_INDEXES;
STATUS
-----------
N/A
UNUSABLE
VALID



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


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';

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';