1.查询索引信息

set echo off
set feedback off
set timing off
set verify off
set LINESHOW off
set pagesize 1000
set linesize 1000

SET DEFINE OFF
SET DEFINE #
SET DEFINE on

select upper(b.table_owner) || '.' || upper(b.table_name) table_name,
       b.index_name,
       b.column_position,
       b.descend,
       b.column_name
from dba_ind_columns b
where upper(b.table_owner) = upper(nvl('#v_owner',b.table_owner))
and upper(b.table_name) like upper('#v_tab_name')
order by b.table_owner||'.'||b.table_name,b.index_name, b.column_position, b.column_name
;

2.查询无效索引

set echo off
set feedback off
set timing off
set verify off
set lineshow off

select a.table_owner||'.'||a.table_name table_name,
       a.owner||'.'||a.index_name       index_name,
       a.status,
       b.last_ddl_time                  last_ddl_time
from dba_indexes a,
     dba_objects b
where b.status not in ('VALID', 'N/A')
and   b.object_name=a.index_name
and   b.owner=a.owner;

3.查询分区表无效索引

set echo off
set feedback off
set timing off
set verify off
set lineshow off

select
        b.table_owner
        ||'.'
        ||b.table_name table_name,
        a.SCH_NAME
        ||'.'
        ||a.index_name index_name ,
        a.partition_name part_name,
        C.status                  ,
        b.index_name              ,
        c.last_ddl_time last_ddl_time
from
        USER_IND_PARTITIONS a
left join dba_indexes b
on
        a.index_name=b.index_name
    and a.SCH_NAME  =b.owner
left join dba_objects c
on
        c.owner      =a.SCH_NAME
    and c.object_name=a.index_name
    AND c.OBJECT_TYPE='INDEX'
where
        a.status not in ('N/A', 'USABLE')

3.查询子分区无效索引

set echo off
set feedback off
set timing off
set verify off
set lineshow off

select b.table_owner||'.'||b.table_name table_name,
       a.SCH_NAME||'.'||a.index_name index_name,
       a.partition_name                 part_name,
       a.subpartition_name              subpart_name,
       a.status
from USER_IND_SUBPARTITIONS a,
     dba_indexes           b
where a.index_name=b.index_name
and   a.SCH_NAME=b.owner
and   a.status not in ('USABLE');
prompt