Index Status Types in DBA_INDEXES

SELECT DISTINCT STATUS FROM DBA_INDEXES;

STATUS

-----------

N/A

UNUSABLE

VALID

N/A

N/A indicates that index is a partitioned index. This is explicitly enforced by catalog.sql. To get the status of the partitioned index query DBA_IND_PARTITIONS:

DBA_IND_PARTITIONS displays the statistics for each partition in the
index. DBA_INDEXES only gives a high level view of the index. Each
partition can have its own status so you need to query DBA_IND_PARTITIONS.

select table_name, INDEX_TYPE, PARTITIONED, status from dba_indexes where status=’N/A';

Unusable indexes

Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as 'unusable' with an ALTER INDEX command. A direct path load against a table or partition will also leave its indexes unusable.

Queries and other operations against a table with unusable indexes will generate errors:

ORA-01502: index ‘string.string’ or partition of such index is in unusable state

The following SQL commands can be used to detect unusable indexes:

indexes:

SELECT owner, index_name, tablespace_name

FROM   dba_indexes

WHERE  status = 'UNUSABLE';

Index partitions:

SELECT index_owner, index_name, partition_name, tablespace_name

FROM   dba_ind_PARTITIONS

WHERE  status = 'UNUSABLE';

The following SQL will print out a list of alter commands that can be executed to fix unusable indexes:

Indexes:

SELECT 'alter index '||index_name||' rebuild tablespace |tablespace_name ||';'

FROM   dba_indexes

WHERE  status = 'UNUSABLE';

Index partitions:

SELECT 'alter index '||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';'

FROM   dba_ind_partitions

WHERE  status = 'UNUSABLE';

For more info check this link

http://www.orafaq.com/node/2384

Why same index status is 'VALID' in dba_objects, but listed as 'UNUSABLE' in dba_indexes

dba_objects just has:

      decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),

whereas dba_indexes has:

       decode(bitand(i.property, 2), 2, 'N/A',

                 decode(bitand(i.flags, 1), 1, 'UNUSABLE',

                            decode(bitand(i.flags, 8), 8, 'INPROGRS',

                                                            'VALID'))),

dba_objects is a generic view, having status in there today is probably "obsolete" as so

much stuff has been added since this view was originally created.  UNUSABLE is a

relatively new attribute of an index.

DBA_OBJECTS must be for every object -- very generic.

DBA_INDEXES -- only for indexes, very specific.