清空分区导致ORA-01502：索引或这类索引的分区处于不可用状态前言一、参考总结二、实用的解决方法总结

96 阅读 0 评论 64 点赞

我是靠谱客的博主友好乌龟，最近开发中收集的这篇文章主要介绍清空分区导致ORA-01502：索引或这类索引的分区处于不可用状态前言一、参考总结二、实用的解决方法总结，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

清空分区导致ORA-01502：索引或这类索引的分区处于不可用状态

前言
一、参考总结
二、实用的解决方法
- 1.清空分区后重建UNUSABLE索引
- 2.测试分析
总结

前言

针对oracle报错的一种解决方法——存储过程：在删除表分区的数据之后，重建所有UNUSABLE索引。

一、参考总结

参考了两篇文章，在此做个总结：
1、
《Oracle分区表删除分区引发错误ORA-01502: 索引或这类索引的分区处于不可用状态》
https://www.cnblogs.com/lijiaman/p/9277149.html
文章总结了（1）索引不可用的三种原因，如下图所示；（2）在删除分区的时候，导致分区表上的唯一性全局索引为不可用状态，导致新的数据无法正常插入，从而引发了该错误；（3）解决问题的三种方法。
在这里插入图片描述
2、
《"ora-01502 索引或这类索引的分区处于不可用状态"的解决方案》
https://blog.csdn.net/qq_36276335/article/details/54287555
该篇文章给出了重建UNUSABLE索引的方法。

二、实用的解决方法

1.清空分区后重建UNUSABLE索引

代码如下：

CREATE OR REPLACE PROCEDURE trunc_par(
    tableName      in varchar2  --RDS表名（非空）
)
AS
  partionName varchar2(20);            --分区名
  execSql    varchar2(1000);    --拼接SQL语句
BEGIN

  --获取索引名：查询年和月，确认分区名，格式如202101
  select to_char(Enddate, 'YYYYMM') into partionName from tableName;
  
  --清空分区中的数据
  execSql:='ALTER TABLE '||tableName||' TRUNCATE PARTITION ' || partionName;
  --打印执行的sql
  --dbms_output.put_line('1、' || execSql);

  EXECUTE IMMEDIATE execSql;
  commit;

  --重建非临时且无效的索引
  for idx in (select index_name from user_indexes where table_name = tableName and temporary = 'N' and status = 'UNUSABLE') loop
        
        execSql := 'alter index ' || idx.index_name || ' rebuild ';
        --打印执行的sql
        --dbms_output.put_line('2、' || execSql);
        EXECUTE IMMEDIATE execSql;
   end loop;

END;

2.测试分析

下面是一些辅助理解的测试分析，如果感兴趣的可以继续细看。以表BDTSXXB（拼音名）为例。

1、Truncate分区后，索引状态为UNUSABLE
代码如下：

--清空表BDTSXXB的分区PAR_202012
alter table BDTSXXB truncate partition PAR_202012;

--查看表BDTSXXB分区的索引状态
select  i.INDEX_NAME,i.UNIQUENESS,i.status from user_indexes i where i.table_name = 'BDTSXXB' ;

查询结果如下图：
清空分区前，索引状态为VALID-有效的
在这里插入图片描述
清空分区后，所有索引状态为UNUSABLE-无效的。注意：分区中有数据时，Truncate分区才会改变索引状态。

2、参考总结的第一篇文章可以知道，唯一性全局索引为不可用状态，导致新的数据无法正常插入，从而引发了该错误。
对唯一性索引重建后，就可以解决插入数据时报错ORA-01502。

代码如下：

--查询唯一性索引
select  i.INDEX_NAME from user_indexes i where i.table_name = 'BDTSXXB' and i.UNIQUENESS='UNIQUE';

--唯一性索引重建
alter index SYS_C0040975 rebuild;