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;



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

2 Trackbacks

Leave a comment