Oracle index in UNUSABLE state

Oracle indexes may get into UNUSABLE state due to multiple reasons – one of them could be a partition of a Partitioned table containing Global indexes was dropped without using ‘UPDATE INDEXES’ option.

Identifying indexes that are in UNUSABLE state (for a table):-
select *
from all_indexes where status = 'UNUSABLE' AND TABLE_NAME = 'my_table';

Rebuild the indexes so that they change to ‘VALID’ state. (use ‘Online’ option if the table is continuously used)

select 'alter index ' || OWNER || '.' || INDEX_NAME || ' rebuild online;'
from all_indexes where status = 'UNUSABLE' AND TABLE_NAME = 'my_table';