select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status = 'UNUSABLE'
union
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from dba_ind_partitions where status = 'UNUSABLE'
union
select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||';' from dba_ind_subpartitions where status = 'UNUSABLE'
/
