oracle查询索引是否生效

数据库 专栏收录该内容
17 篇文章 0 订阅
以下脚本可用于列出数据库中的失效的索引、索引分区、子分区:
 
如果不是失效的索引,那么都是有效的
 
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
/

  • 1
    点赞
  • 0
    评论
  • 3
    收藏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值