Why and How to prepare a rebuild indexes using a wild select to generate all scripts?

 Why ?

Indexes will need a rebuild for certain cases and some cases it will be optional or have a different way to do it as well, however here I am sharing a situation where you will be must do this rebuild when the status become unusable.

How ?

Instead of do a manual rebuild for your indexes it is easier and more accurate if you use the below script to create the rebuild, keep on your mind that online rebuild and parallel and nologing are feature you have to measure it and use whatever is suitable for your case :

The main structure of this rebuild options, logging is the default option:

alter index my_idx rebuild online parallel 63 nologging; 
alter index my_idx noparallel;
alter index my_idx logging;

1- You may use the below for non-partition index:

select 'alter index '||owner||'.'||index_name||' REBUILD ONLINE;' from dba_indexes where status='UNUSABLE' and index_type not in ('LOB');;

2- You may use the below for partition index:

select 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION ' ||partition_name||' ONLINE;' from DBA_IND_PARTITIONS where status='UNUSABLE' and index_type not in ('LOB');