Sunday, 10 February 2013

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

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.