oracle rebuild unusable indexes code example

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