How to find unusable indexes in oracle database and how to rebuild the unusable indexes

This document describes how to find unusable indexes in oracle database and how to rebuild unusable indexes to make it use.

In some scenarios we get unusable indexes in the database. We have to find which indexes are unusable and we have to rebuild them to make it use.

Use below command to find unusable indexes in the database.

col owner for a15
col index_name for a35
col table_name for a40 
select owner,index_name,status,table_name from dba_indexes where status='UNUSABLE';

Use below command to rebuild indexes.

alter index <owner>.<index name> rebuild online;

SQL> alter index XXEMPLOYEES_EMPNO_T1 rebuild online;

Thanks for going through the post......................