ORA-01502: index ‘%’ or partition of such index is in unusable state

To Solve this error you could use more than one solution :

Solution One :

declare
begin
   for index_rec in (select owner, index_name
                     from dba_INDEXES
                     where status = ‘UNUSABLE’)
   loop                    
      execute immediate ‘alter index ‘ || index_rec.owner || ‘.’ ||
          index_rec.index_name || ‘ rebuild’;
   end loop;
end;

Solution two:

10g and above :

alter system set skip_unusable_indexes=true;

 thank you
Osama mustafa 

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.