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:

[sourcecode language=’sql’]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;[/sourcecode]

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 *