Google it ....

Showing posts with label ORA-01502. Show all posts
Showing posts with label ORA-01502. Show all posts

Friday, August 15, 2014

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.