Home > DBA Scripts, Errors, Oracle > Oracle – Fix unusable indexes

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

  1. Gregor
    October 21, 2013 at 9:00 AM

    Thanks for this note! It saved me a lot of time looking for a solution on my problem.

    If you insert the owner into the statements, the script could be executed as SYSDBA:

    select ‘alter index “‘|| owner || ‘”.”‘ ||index_name||'” rebuild;’ from all_indexes where status =’UNUSABLE’;

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: