PSST0101

ORA-01502: Indexes in unusable state

Advertisements

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;
Advertisements

Advertisements