以下脚本可用于列出数据库中的失效的索引、索引分区、子分区:
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
/
