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