How to find unusable indexes and generate REBUILD statements in Oracle

Two-part script: first lists every unusable index, partition, and subpartition, then generates the ALTER INDEX ... REBUILD statements you would run to fix them. Verify the output before running the rebuilds — large indexes can consume significant temp and undo.

The second query only generates statements; nothing changes until you copy them and run them. Rebuilds are online-friendly with ONLINE clause (Enterprise Edition), otherwise they lock the object.

-- 1) List every unusable index / partition / subpartition
SELECT index_name AS name,
       'No Partition'    AS partition,
       'No Subpartition' AS subpartition,
       status
FROM   all_indexes
WHERE  status NOT IN ('VALID', 'USABLE', 'N/A')
UNION
SELECT index_name,
       partition_name,
       'No Subpartition',
       status
FROM   all_ind_partitions
WHERE  status NOT IN ('VALID', 'USABLE', 'N/A')
UNION
SELECT index_name,
       partition_name,
       subpartition_name,
       status
FROM   all_ind_subpartitions
WHERE  status NOT IN ('VALID', 'USABLE', 'N/A');


-- 2) Generate the ALTER INDEX REBUILD statements
SELECT 'ALTER INDEX ' || owner || '.' || index_name
       || ' REBUILD TABLESPACE ' || tablespace_name || ';' AS ddl
FROM   dba_indexes
WHERE  status = 'UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || index_owner || '.' || index_name
       || ' REBUILD PARTITION ' || partition_name
       || ' TABLESPACE ' || tablespace_name || ';' AS ddl
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || index_owner || '.' || index_name
       || ' REBUILD SUBPARTITION ' || subpartition_name
       || ' TABLESPACE ' || tablespace_name || ';' AS ddl
FROM   dba_ind_subpartitions
WHERE  status = 'UNUSABLE';