ORACLE 数据库：如何修复失效的索引

1、问题描述：ERP系统在运行时，有时会遇上这种错误：ORA-01502：index 'K3CLOUD.IDX_BD_MATER_OLDNUM' or partition of such index is in unusable state

这意思是，语句在执行时，需要使用索引，但索引失效（UNUSABLE，不可用）了。数据库反馈这个错误，意在告诉运维人员，需要修复失效的索引。

2、问题分析：我们可以在数据库里，使用下面的语句，查询失效的索引：

select ui.table_name, ui.index_name, ui.subobject_name, parted, to_char(uo.last_ddl_time,'yyyy/mm/dd hh24:mi:ss') last_ddl_time

            from (select table_name, index_name, null subobject_name, 'NO' parted, status from user_indexes where status='UNUSABLE'

                  and partitioned='NO' and temporary='N' and index_type like '%NORMAL%'

                  union all

                  select ui.table_name, ui.index_name, uip.partition_name subobject_name, 'PART' parted, uip.status from user_ind_partitions uip

                  inner join user_indexes ui on (ui.index_name=uip.index_name) where uip.status='UNUSABLE'

                  and ui.partitioned='YES' and ui.temporary='N' and ui.index_type like '%NORMAL%'

                  union all

                  select ui.table_name, ui.index_name, uisp.subpartition_name subobject_name, 'SUBPART' parted, uisp.status from user_ind_subpartitions uisp

                  inner join user_ind_partitions uip on (uip.index_name=uisp.index_name and uip.partition_name=uisp.partition_name)

                  inner join user_indexes ui on (ui.index_name=uip.index_name)

                  where uisp.status='UNUSABLE' and ui.partitioned='YES' and ui.temporary='N' and ui.index_type like '%NORMAL%') ui

            left join user_objects uo on (uo.obje