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
/

© 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. Script:列出没有主键或唯一索引的表
2. Script:列出数据库中5%以上链式行的表
3. Script:收集数据库安全风险评估信息
4. Script:列出数据库中子表上没有对应索引的外键
5. Script:List Buffer Cache Details
6. Script:Datafile Report
7. Script:To Report Information on Indexes
8. Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
9. Script:收集Oracle备份恢复信息