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:

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

7 Responses to “ORA-01502: Indexes in unusable state”

  1. dario Says:

    very good answer, thank you pal,
    this information is very important

  2. luong truong vu Says:

    Thanks. ideas is very good

  3. Orly Says:

    thanks for sharing the answer. that was very helpful.

  4. ¤.§íĈÅRïð.¤ Says:

    Buen PL. Me ha sido de mucha utilidad… gracias

  5. Teena Says:


    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??

  6. digitaleagle Says:


    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.

  7. 2010 in review « PSST0101 Says:

    […] ORA-01502: Indexes in unusable state February 2008 6 comments 4 […]

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

%d bloggers like this: