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