oracle rebuild unusable indexes
-- Indexes:
SELECT 'alter index ' || OWNER || '.' || INDEX_NAME || ' rebuild tablespace '
|| TABLESPACE_NAME || ';' 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 || ';' 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 || ';' SQL_TO_REBUILD_INDEX
FROM DBA_IND_SUBPARTITIONS WHERE STATUS = 'UNUSABLE';