ORA-01502: Indexes in unusable state

I kept getting error messages like this:

ORA-01502: index 'SYSADM.PS_PSAESTMTDEFN' or partition of such index is in unusable state

I found the answer to the problem here:

ORA-01502 Oracle Index in Unusable State

As Katie mentioned in the comments, the status in 10g is UNUSABLE instead of INVALID.

Here is a quick script to rebuild all of the problem indexes:

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;

8 thoughts on “ORA-01502: Indexes in unusable state

  1. Hi,

    Great answer…
    But just having a question..
    we have three Databases , all in 10 G but I am getting this error only in 1 Datbase only

    Why not in all??

  2. Teena,

    I don’t know the answer. I think my problem was caused by some work that I did moving and adjusting the tablespaces. Maybe something was running while you refreshed or restored one of the databases that did not happen on the other databases. Maybe something happened that affected only the database files for the one database. There is no telling.

    Hope that helps give you some clues. I am glad the post helped, and thanks also to “My Digital Life” for the original answer.

Leave a Comment

Your email address will not be published. Required fields are marked *

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