列出失效索引或索引分区

来源：互联网发布：jsp页面调用java方法编辑：程序博客网时间：2024/06/05 14:46

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

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;