ORA-01502 index or partition of such index is in usable state

After moving indexes to another tablespaces, when developer tried to alter the table they got an error, ORA-01502 index or partition of such index is in usable state.

When checked i found many indexes from schema are in unusable state.

SQL> select index_name, status from dba_indexes where status ='UNUSABLE' and owner='PROD';

INDEX_NAME                     STATUS 
------------------------------ -------- 
MODFAM_FULLNAME                UNUSABLE 
BLOB_TYPESUBTYPEFIRSTKEYN      UNUSABLE 
BLOB_TYPEFIRSTKEYSUBTYPEN      UNUSABLE

To fix this issue, we need to rebuild the indexes. Indexes can be rebuild online also.

List down the unusable indexes & rebuild it.

SQL> spool /home/oracle/unusable_index_prod.sql

SQL> select 'alter index '||owner||'.'||index_name||' rebuild online;' from dba_indexes where status='UNUSABLE' and owner='PROD';

SQL> spool off;

SQL> spool /home/oracle/unusable_index_prod.log

SQL> @/home/oracle/unusable_index_prod.sql

SQL> spool off;

After running this we can check again, if there are any unusable indexes.

SQL> select index_name, status from dba_indexes where status ='UNUSABLE' and owner='PROD';

SQL> no rows selected.

Once it is complete, update the statistics on schema.

SQL> EXEC DBMS_STATS.gather_schema_stats('PROD', estimate_percent => 25, cascade => TRUE);