I was recently trying to clear down the SYSAUX tablespace that had filled with fragmented statistics retention indexes and tables.
However, when I tried to rebuild one of the indexes I received the following error:
SQL> alter index I_WRI$_OPTSTAT_H_ST rebuild;
alter index I_WRI$_OPTSTAT_H_ST rebuild
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST' or partition of such index is in unusable state
I queried the sys.all_indexes table to find which indexes were in an unusable state.
SQL> select index_name, status from all_indexes where status ='UNUSABLE';
INDEX_NAME STATUS
------------------------------ --------
I_WRI$_OPTSTAT_IND_OBJ#_ST UNUSABLE
I_WRI$_OPTSTAT_IND_ST UNUSABLE
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST UNUSABLE
I_WRI$_OPTSTAT_H_ST UNUSABLE
To resolve the issue I rebuild the offending index and all was well and good again.
SQL> alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;
Index altered.
