index is in unusable state

Error Messsage

While compiling the flexfields, the procedure wf_event.raise failed with following
parameters:
event_name = oracle.apps.fnd.flex.dff.compiled
event_key = FND.$SRS$.FNDSCURS

The error raised is:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'APPLSYS.SYS_C00169351' or partition of such index is in unusable state

Check the Unstale index

select index_name, status from dba_indexes where tablespace_name='APPS_TS_QUEUES' and STATUS != 'VALID';

WF_JAVA_DEFERRED_N1 UNUSABLE
SYS_C00169351 UNUSABLE
WF_DEFERRED_N1 UNUSABLE
SYS_C00105999 UNUSABLE


Solution:

Rebuild Unstale index.


select 'alter index 'owner'.'index_name' rebuild;' from dba_indexes where tablespace_name='APPS_TS_QUEUES' and STATUS != 'VALID';