Reference : [[https://www.orafaq.com/wiki/Unusable_indexes|Unusable indexes - orafaq.com - This page is too good to miss if it ever gets taken down]]
==== Unusable indexes ====
Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as 'unusable' with an ALTER INDEX command. A direct path load against a table or partition will also leave its indexes unusable.\\
Queries and other operations against a table with unusable indexes will generate errors:\\
ORA-01502: index ‘string.string’ or partition of such index is in unusable state\\
=== Detecting ===

The following SQL commands can be used to detect unusable indexes:\\
  *  Indexes:
<code>
SELECT owner, index_name, tablespace_name
FROM   dba_indexes
WHERE  status = 'UNUSABLE';
</code>
  *  Index partitions:
<code>
SELECT index_owner, index_name, partition_name, tablespace_name
FROM   dba_ind_PARTITIONS
WHERE  status = 'UNUSABLE';
</code>
  *  Index subpartitions:
<code>
SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name
FROM   dba_ind_SUBPARTITIONS
WHERE  status = 'UNUSABLE';
</code>
=== Fixing ===

The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:\\
  *  Indexes:
<code>
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||';' sql_to_rebuild_index
FROM   dba_indexes
WHERE  status = 'UNUSABLE';
</code>
  *  Index partitions:
<code>
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';' sql_to_rebuild_index
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE';
</code>
  *  Index subpartitions:
<code>
SELECT 'alter index '||index_owner||'.'||index_name ||' rebuild subpartition '||SUBPARTITION_NAME||' TABLESPACE '||tablespace_name ||';' sql_to_rebuild_index
FROM   dba_ind_subpartitions
WHERE  status = 'UNUSABLE';
</code>
or if you prefer via single PLSQL anonymous block:
<code>
set serveroutput on size unlimited

BEGIN
	FOR x IN
	(
		SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE PARALLEL' comm
		FROM    dba_indexes
		WHERE   status = 'UNUSABLE'
		UNION ALL
		SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||partition_name||' ONLINE PARALLEL'
		FROM    dba_ind_PARTITIONS
		WHERE   status = 'UNUSABLE'
		UNION ALL
		SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD SUBPARTITION '||subpartition_name||' ONLINE PARALLEL'
		FROM    dba_ind_SUBPARTITIONS
		WHERE   status = 'UNUSABLE'
	)
	LOOP
		dbms_output.put_line(x.comm);
		EXECUTE immediate x.comm;
	END LOOP;
END;
/
</code>