Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01502 : index SYS.I_PROCEDURE1 is in unusable state

bderousOct 29 2012 — edited Oct 29 2012
Oracle 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 26 2012
Added on Oct 29 2012
4 comments
1,232 views