ORA-01502 : index SYS.I_PROCEDURE1 is in unusable state
bderousOct 29 2012 — edited Oct 29 2012Oracle EE 9.2.0.8 on Windows 2003 SP2
Some SYS tables where moved by mistake with statement : "alter table sys.xxx move tablespace system;"
This caused some unusable SYS indexes but I cannot rebuild them. Each time I get same error :
ORA-01502: index 'SYS.I_PROCEDURE1' or partition of such index is in unusable state
Tracing showed me that some recursive SQL is going on and that fails because of the unusable index I_PROCEDURE1
This is the recursive sql who fails :
SQL> select audit$,options from procedure$ where obj#=115;
*
ERROR at line 1:
ORA-01502: index 'SYS.I_PROCEDURE1' or partition of such index is in unusable state
How do get out of this situation ?
Have already tried without succes :
- alter session set skip_unusable_indexes=true;
- altered some parameters to favor full table scans (optimizer_mode, db_file_multi_block_read_count
- tried to drop index (same error)
Thanks.
Benny Derous