How to rebuild unusable indexes partition in oracle

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s