Oracle分区表删除分区引发错误ORA-01502: 索引或这类索引的分区处于不可用状态

概述（一）问题： 最近在做Oracle数据清理，在对分区表进行数据清理时，采用的方法是drop partition，删除的过程中，没有遇到任何问题，大概过了10分钟，开发人员反馈部分分区表上的业务失败。具

（一）问题：

最近在做Oracle数据清理，在对分区表进行数据清理时，采用的方法是drop partition，删除的过程中，没有遇到任何问题，大概过了10分钟，开发人员反馈部分分区表上的业务失败。具体错误为：

ORA-01502错误：索引或这类索引的分区处于不可用状态（英文：ora-01502:index 'schema.index_name' or partition of such index is in unusable state）。

（二）原因分析

查看出现问题的分区表，均有一个共同点：表上以“pk_”开头的索引为unusable状态，以“pk_”开头的索引是随创建主键约束而创建的。当用户在创建主键约束或唯一性约束的时候，会在相应的列上创建唯一性索引

经过查证，发现是在删除分区的时候，导致分区表上的唯一性全局索引为不可用状态，导致新的数据无法正常插入，从而引发了该错误。

是不是索引不可用会导致DML *** 作失败呢？经过验证，发现以下特点：

1.对于非唯一性索引，如果索引不可用，是不会影响到到DML *** 作的；

2.对于唯一性索引，如果索引不可用，在进行DML *** 作时，会触发ORA-01502错误；

 

这里记录一下哪些 *** 作会导致索引失效：

                                                              图1.索引失效原因总结

（三）解决方案

（3.1）了解唯一性索引

在解决问题之前，我们来分析一下，哪些行为会创建唯一性索引(3种)：

--直接创建唯一性索引。

     语法为：CREATE UNIQUE INDEX index_name on table_name(col1，col2,…);

--创建主键约束时自动创建唯一性索引。

     语法为：ALTER table table_name ADD CONSTRAINT constraint_name PRIMARY KEY(col1,col2,..);

--创建唯一性约束时自动创建唯一性索引。

     语法为：ALTER table table_name ADD CONSTRAINT constraint_name UNIQUE(col1,…);

 

这里，我总结了3套方案来对应ORA-01502问题。

（3.2）方案一：删除唯一性索引

与业务方面沟通，确认唯一性索引是否可以删除，如果可以，直接删除索引，删除语法为：

sql> DROP INDEX schema.index_name;

对于由主键约束或唯一性约束创建的唯一性索引，不能直接删除

drop index lijiaman.sale_pk; lijiaman.sale_pkORA-02429: cannot drop index used for enforcement of unique/primary key

正确的方法是删除相应的约束。

alter table sales constraint sale_pk;table altered

小结：该方法简单粗暴，前提是在约束或索引在业务方面可以删除的情况下才能使用。

（3.3）方案二：重建唯一性索引（针对非分区表）

语法为：

ALTER INDEX [schema.]index_name REBUILD [ONliNE] tableSPACE tablespace name]

小结：该方法可以使索引可用。但对于分区表而言，依然存在问题：在下一次删除分区后，索引状态又会变为不可用。

（3.4）方案三：删除不可用的索引，创建唯一性分区索引（针对分区表）

创建唯一性分区索引：

CREATE UNIQUE INDEX index_name on schema.]table(col1,...);

对于主键约束、唯一性约束，可以使用以下语法添加唯一性局部分区索引：

table ]table_name ADD CONSTRAINT constarint PRIMARY KEY | UNIQUE(col1,col2) USING INDEX LOCAL tableSPACE tablespace_name;

小结：该方法可以有效解决分区表因删除分区导致的索引不可用问题。

 

 

附录：模拟实验

（1）首先模拟生产情况，创建一张表：

create table sales(  prod_ID number,cust_ID number(3))partition by range(time_ID)(  partition sales_q1_2017 values less than(to_date('1-4-2017',dd-mm-yyyy')),partition sales_q2_2017 1-7-20171-10-20171-1-2018)));

插入数据,确保每个分区都有数据

insert into sales(prod_ID,cust_ID,time_ID,quantity_sold)values(1,11,to_date(2017-02-01yyyy-mm-dd'),1)">103);2,1)">12,1)">2017-06-013,1)">14,1)">2017-08-014,1)">2017-12-01103);

检查一下数据库的数据信息

select * from sales; --查看整个分区表的数据 PROD_ID CUST_ID TIME_ID QUANTITY_SolD-------- ---------- ----------- ------------- 1 11 2017/21 103 2 12 63 14 84 121 sqlfrom sales partition(sales_q1_2017); 查看分区“sales_q1_2017”的数据103

（2）由于出现ORA-01502问题时，与表相关的对象只有主键约束和索引。所以，我在表上创建了索引和约束，并确认了所有索引可用

add constraint sale_pk primary key(time_ID,cust_ID); 创建主键约束index inx_sales_1 on sales(cust_ID); 创建普通(全局)索引index inx_sales_2 on sales(time_ID) local; 创建局部分区索引

确认索引状态：

select owner,table_name,index_name,status 2 from dba_indexes 3 where owner = liJIAMAN' 4 and table_name SALES;OWNER table_name INDEX_name STATUS---------------------------- ------------------------------ ------------------------------ --------liJIAMAN SALES INX_SALES_2 NAliJIAMAN SALES SALE_PK VALIDliJIAMAN SALES INX_SALES_1 VALID

对于索引“SALES_PK”和“INX_SALES_1”,索引状态为可用，那”INX_SALES_2“这个索引状态为”N/A“，这又是怎么回事么？经过查找资料，确认索引共有四种状态：

N/A         ：说明这个是分区索引需要查user_ind_partitions或者user_ind_subpartitions来确定每个分区是否可用； VAILD      ：说明这个索引可用； UNUSABLE：说明这个索引不可用； USABLE    ：说明这个索引的分区是可用的。

我们再去查看数据字典DBA_IND_PARTITIONS，确认”INX_SALES_2”的状态，索引可用。

SELECT INDEX_OWNER,INDEX_name,PARTITION_name,STATUS FROM DBA_IND_PARTITIONS I WHERE I.INDEX_OWNER ' AND I.INDEX_name INX_SALES_2;INDEX_OWNER INDEX_name PARTITION_name STATUS---------------------------- ------------------------------ ------------------------------ --------liJIAMAN INX_SALES_2 SALES_Q1_2017 USABLEliJIAMAN INX_SALES_2 SALES_Q2_2017 USABLEliJIAMAN INX_SALES_2 SALES_Q3_2017 USABLEliJIAMAN INX_SALES_2 SALES_Q4_2017 USABLE

确认主键约束的状态,确认可用

dba_constraints ;OWNER table_name CONSTRAINT_name CONSTRAINT_TYPE STATUS DEFERRABLE DEFERRED VALIDATED------- ------------ ----------------- --------------- -------- -------------- --------- -------------liJIAMAN SALES SALE_PK P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

（3）接下来，我们模拟数据清理，删除分区”sales_q1_2017“

drop partition sales_q1_2017 ;table altered

查看分区表的数据，可以看到，分区”sales_q1_2017“的数据已经随着分区被删除

sales; PROD_ID CUST_ID TIME_ID QUANTITY_SolD103

 

==================转折点==========================

 

（4）此时，我们模拟正常的业务交易，发现如下情况

    --对于insert *** 作，无法完成，报ORA-01502错误；

    --对于delete *** 作，无法完成，报ORA-01502错误；

    --对于update *** 作，如果不涉及到主键相关的列，则可以执行成功，如果涉及到主键列，报ORA-01502错误；

数据插入测试，发现无法插入数据sql5,1)">15,1)">2017-8-01)ORA01502: index liJIAMAN.SALE_PKor partition of such index is in unusable state数据删除测试，发现无法删除数据sqldelete from sales where prod_ID = ;ORA数据跟新测试，测试3次，发现涉及到与主键相关的列，就会更新失败，其他情况更新成功sqlupdate sales set QUANTITY_SolD 105 where PROD_ID 2;1 row updatedsqlcommit;Commit completesqlwhere cust_ID 12set cust_ID 15 in unusable state

再次确认，索引的状态，可以看到，普通索引状态已经转变为不可用，而局部分区索引状态未发生改变。

AliJIAMAN SALES SALE_PK UNUSABLEliJIAMAN SALES INX_SALES_1 UNUSABLEsqlliJIAMAN INX_SALES_2 SALES_Q2_2017 USABLEliJIAMAN INX_SALES_2 SALES_Q3_2017 USABLEliJIAMAN INX_SALES_2 SALES_Q4_2017 USABLE

主键约束状态也为发生改变

OWNER table_name CONSTRAINT_name CONSTRAINT_TYPE STATUS DEFERRABLE DEFERRED VALIDATED-------- ------------ ---------------- --------------- -------- -------------- --------- -------------liJIAMAN SALES SALE_PK P ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

这里，我们对deop分区前后表的信息对比做一个小结

	删除分区前	删除分区后
查询（select）	正常	正常
插入（insert）	正常	无法插入
删除（delete）	正常	无法删除
更新（update）	正常	设计到主键相关的列，更新失败，其他情况更新成功
约束状态（constraint）	可用	可用
索引状态（index）	全部可用	1.分区索引可用 2.主键约束上的唯一性索引不可用 3.普通索引不可用

通过对比，我们可以推测，索引不可用导致了无法正常DML *** 作。那么到底是哪个索引导致的问题呢？

（5）首先测试普通索引，先重建索引INX_SALES_1

lijiaman.inx_sales_1 rebuild;Index alteredsql'liJIAMANAliJIAMAN SALES SALE_PK UNUSABLEliJIAMAN SALES INX_SALES_1 VALID

继续模拟DML交易,情况与删除分区后的DML结果相同，可以确认，普通索引不可用并不会引起DML *** 作失败

数据依然无法插入sql)ORAin unusable state数据无法删除sqlORA如果没有更新到逐渐相关列，可以更新数据，否则不行sql row updatedsqlin unusable state

（6）接着重建唯一性索引”SALE_PK”

lijiaman.sale_pk rebuild;Index altered

对SALES表进行DML *** 作，可以正常进行

); row insertedsql ; row deletedsqlCommit complete

至此，我们可以大胆猜测：唯一性索引导致的ORA-01502问题。由于我们在创建索引的时候，并未直接创建唯一性索引，而是在创建主键约束的时候自动创建的唯一性索引，那么到底是主键约束的问题，还是唯一性索引的问题？根据上面删除分区前后约束状态相同，而索引状态不同，我觉得是索引的问题。继续求证。我们新建一个表，在上面直接创建唯一性索引，不创建任何约束。

创建表test01,录入数据

test01; ID name AGE-------- -------------------- ---------- lijiaman 2 gegeman 25 3 xiaoman 26 4 lijiaman 25

在“ID”列创建唯一性索引

unique index inx_test01 on test01(ID); createdsql table_owner,index_type,uniqueness,1)"> user_indexes where table_name TEST01;table_OWNER INDEX_name INDEX_TYPE UNIQUEnesS STATUS---------------------------- ------------------------------ --------------------------- ---------- --------liJIAMAN INX_TEST01 norMAL UNIQUE VALID

插入数据，没有异常

into test01 bokeyuan22);1 row inserted

接着将索引置为不可用状态，然后往表里面插入数据，出现了01502错误；

index lijiaman.inx_test01 unusable; 将索引置为不可用状态UNIQUE UNUSABLEsql25); 插入数据，发生ORA-01502错误25liJIAMAN.INX_TEST01in unusable state

至此可以明确的说：ORA-01512错误是由于唯一性索引失效导致的。

如何解决这个问题，前面给出了3种方案，只要选择其中一种即可，不再模拟。

总结

以上是内存溢出为你收集整理的Oracle分区表删除分区引发错误ORA-01502: 索引或这类索引的分区处于不可用状态全部内容，希望文章能够帮你解决Oracle分区表删除分区引发错误ORA-01502: 索引或这类索引的分区处于不可用状态所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。