Check Invalid or Unusable Indexes

 
Here’s a script i use to check for invalid or unusable indexes.

For checking regular/unpartitioned indexes:


set lines 200
col owner for a15
col index_name for a30
col table_name for a30
col status for a10
select owner,
index_name,
table_name,
status
from dba_indexes
where status not in ('VALID','N/A');

For checking partitioned indexes:


set lines 200
col index_owner for a15
col index_name for a30
col partition_name for a30
col status for a10
select index_owner,
index_name,
partition_name,
status
from dba_ind_partitions
where status not in ('USABLE','N/A');

For checking subpartitioned indexes:


set lines 200
col index_owner for a15
col index_name for a30
col subpartition_name for a30
col status for a10
select index_owner,
index_name,
subpartition_name,
status
from dba_ind_subpartitions
where status not in ('USABLE','N/A');

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart