Oracle – Fix unusable indexes

If you want to find a list of unusable indexes and fix them you can issue the following SQL that will print out a list of alter commands that can be copied and pasted to the SQL command line then executed to fix the indexes.

SQL> select 'alter index '||index_name||' rebuild;' from all_indexes  where status ='UNUSABLE';
alter index I_WRI$_OPTSTAT_HH_ST rebuild;
alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild;
alter index I_WRI$_OPTSTAT_H_ST rebuild;
alter index I_WRI$_OPTSTAT_OPR_STIME rebuild;

This will help resolve errors such as the following:

SQL>  exec dbms_stats.purge_stats(to_date('28-JUN-11','DD-MON-YY'));
BEGIN dbms_stats.purge_stats(to_date('28-JUN-11','DD-MON-YY')); END;
ERROR at line 1:
ORA-01502: index 'SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST' or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 1100
ORA-06512: at "SYS.DBMS_STATS", line 17203
ORA-06512: at line 1

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s