列出失效索引或索引分区

以下脚本可用于列出数据库中的失效的索引

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;