Script:列出失效索引或索引分区

以下脚本可用于列出数据库中的失效的索引、索引分区、子分区:

REM list of the unusable index,index partition,index subpartition in Database 

Select owner, index_name, status
  From dba_indexes
 where status = 'UNUSABLE'
   and owner not in ('SYS','SYSTEM',
                     'SYSMAN',
                     'EXFSYS',
                     'WMSYS',
                     'OLAPSYS',
                     'OUTLN',
                     'DBSNMP',
                     'ORDSYS',
                     'ORDPLUGINS',
                     'MDSYS',
                     'CTXSYS',
                     'AURORA$ORB$UNAUTHENTICATED',
                     'XDB',
                     'FLOWS_030000',
                     'FLOWS_FILES')
 order by 1, 2 
/

select index_owner, index_name, partition_name
  from dba_ind_partitions
 where status ='UNUSABLE'
   and index_owner not in ('SYS',
                           'SYSTEM',
                           'SYSMAN',
                           'EXFSYS',
                           'WMSYS',
                           'OLAPSYS',
                           'OUTLN',
                           'DBSNMP',
                           'ORDSYS',
                           'ORDPLUGINS',
                           'MDSYS',
                           'CTXSYS',
                           'AURORA$ORB$UNAUTHENTICATED',
                           'XDB',
                           'FLOWS_030000',
                           'FLOWS_FILES') order by 1,2
/

Select
       Index_Owner
     , Index_Name
     , partition_name
     , SUBPARTITION_NAME
 From 
       DBA_IND_SUBPARTITIONS
Where
       status = 'UNUSABLE'  
       and index_owner not in ('SYS',
                           'SYSTEM',
                           'SYSMAN',
                           'EXFSYS',
                           'WMSYS',
                           'OLAPSYS',
                           'OUTLN',
                           'DBSNMP',
                           'ORDSYS',
                           'ORDPLUGINS',
                           'MDSYS',
                           'CTXSYS',
                           'AURORA$ORB$UNAUTHENTICATED',
                           'XDB',
                           'FLOWS_030000',
                           'FLOWS_FILES') order by 1, 2
/