oracle查询索引是否生效

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

 

如果不是失效的索引，那么都是有效的

 

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

/