if you find out that you have indexes that are in “unusable state”.

first, list all invalid indexes within your Oracle database:

select * from dba_indexes where STATUS <>‘VALID’;

 

after that, rebuild each index, using sql statement:

alter index schema.index_name rebuild;