ORA-01502: index is in unusable state

When try to execute query on any table with select SQL statement, Oracle returns the following error:

ORA-01502: index 'indexname' or partition of such index is in unusable state

The reason is that some indexes has been marked unusable by a direct load or by a DDL operation, or shrinking space etc. Here is simple solution.

1. Drop the specified index and/or recreate the index
2. Rebuild the specified index
3. Rebuild the unusable index partition

Here we rebuilding UNUSABLE indexes onling, quering to USER_INDEXES view.

$sqlplus /nolog

Sql> connect system/oracle
Sql> SELECT count(*),status FROM all_indexes GROUP BY status;
Sql> SELECT 'alter index 'table_owner'.'index_name' rebuild online ;' FROM all_indexes where status='UNUSABLE' ORDER BY table_owner

Just execute sql statement output, it will rebuild all unused indexes.