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';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.