Oracle - ORA-01502: index XXX.YYYY or partition of such index is in unusable state

If you encounter an Oracle error

ORA-01502: index XXX.YYYY or partition of such index is in unusable state

You could apply the following fix at the database

1. Log into the database, for example

sqlplus sys as sysdba

2. Run the following SQL

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';

3. The above SQL will return multiple Alter statement which the index has to be rebuild. For example

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
--------------------------------------------------------------------------------
ALTER INDEX MYUSER.TABLE1 REBUILD;
ALTER INDEX MYUSER.TABLE2 REBUILD;

4. Run all of the alter statement to make the index usable again and it should fix the issue.

Reference:

1. http://dba.stackexchange.com/questions/3754/ora-01502-index-or-partition-of-such-index-is-in-usable-state-problem