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;
very good answer, thank you pal,
this information is very important
Thanks. ideas is very good
thanks for sharing the answer. that was very helpful.
Buen PL. Me ha sido de mucha utilidad… gracias
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??
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.