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

Hello,
here I'll show you how to find and rebuild unusable indexes partition in oracle:

SQL> select distinct status from dba_ind_partitions;
STATUS
——–
USABLE
UNUSABLE
SQL>select count(1) from dba_ind_partitions where status='UNUSABLE';
  COUNT(1)
———-
         2

we have two unusable indexes, get code for rebuild this indexes

SQL>select  ' alter index '||index_owner||'.'||index_name||' rebuild partition '||
partition_name||';' from dba_ind_partitions where status='UNUSABLE';

and execute that code

SQL>alter index IKA.TEST_TBL_idx rebuild partition P_2014_02;
Index altered.
alter index IKA.TEST_TBL_idx rebuild partition P_2014_03;
Index altered.

check that we have all indexes usable state

SQL>select distinct status from dba_ind_partitions;
STATUS
——–
USABLE

that's all.