Indexing

How to get the status of Indexes: 

SQL> select * from all_indexes where status = 'VALID';

SQL> SELECT INDEX_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED
     FROM   DBA_INDEXES

The status can be 

• VALID 
• INVALID
• N/A
• UNUSABLE

if the status is 'N/A', that is the partitioned index.  
We need to query the partition details in DBA_IND_PARTITIONS to get the 
status of particular partition.

SQL> SELECT PARTITION_NAME, STATUS, SEGMENT_CREATED
     FROM   DBA_IND_PARTITIONS;

UNUSABLE indexes will not hold any space, so below query return no records incase 

of Unusable indexes 

SQL> SELECT SEGMENT_NAME, BYTES
     FROM   USER_SEGMENTS
     WHERE  SEGMENT_NAME IN ('I_EMP_ENAME', 'EMP_EMAIL_UK');