1-- Indexes:
2SELECT 'alter index ' || OWNER || '.' || INDEX_NAME || ' rebuild tablespace '
3 || TABLESPACE_NAME || ';' SQL_TO_REBUILD_INDEX FROM DBA_INDEXES
4WHERE STATUS = 'UNUSABLE';
5-- Index partitions:
6SELECT 'alter index ' || INDEX_OWNER || '.' || INDEX_NAME
7 || ' rebuild partition ' || PARTITION_NAME || ' TABLESPACE '
8 || TABLESPACE_NAME || ';' SQL_TO_REBUILD_INDEX
9FROM DBA_IND_PARTITIONS WHERE STATUS = 'UNUSABLE';
10-- Index subpartitions:
11SELECT 'alter index ' || INDEX_OWNER || '.' || INDEX_NAME
12 || ' rebuild subpartition ' || SUBPARTITION_NAME || ' TABLESPACE '
13 || TABLESPACE_NAME || ';' SQL_TO_REBUILD_INDEX
14FROM DBA_IND_SUBPARTITIONS WHERE STATUS = 'UNUSABLE';