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

概述继上次删除分区表的分区遇到ORA-01502错误后[详细见链接：Oracle分区表删除分区引发错误ORA-01502: 索引或这类索引的分区处于不可用状态]，最近在split分区的时候又遇到了这个问题

继上次删除分区表的分区遇到ORA-01502错误后[详细见链接：Oracle分区表删除分区引发错误ORA-01502: 索引或这类索引的分区处于不可用状态]，最近在split分区的时候又遇到了这个问题。这里记录一下该问题是如何产生的，以及如何去解决。

（一）目的

        在生产中，我们的大多数分区表都是按照时间分区的，最常见的是按周或按月分区，对于我们DBA来说，对表分区的创建与删除都非常好管理，我在2018年10月会将所有表的分区创建到2019年12月，这样2019年的数据就会进入各个对应月份的分区。

　　但是也有小部分分区表是按照其它来分区，例如，事物交易编号等，我们将10万个交易信息存放在一个分区，对于业务，这样创建分区是合理的，但是存在一定的隐患，每天的交易量是动态变化的，有可能3天使用完1个分区，也有可能1天就使用完一个分区，那么分区什么时候使用完我们是不得而知的。对于这种情况，我会为这类分区表添加max分区，从而保证当数据溢出了我们创建的分区时，会进入到max分区里面。分区表大致形式如下(需要说明的是，实际分区表的分区非常大，这里是为了模拟事故创建的小表)：

                                                    图1.表栏位信息

 

                                                              图2.表分区情况

（二）事故起因

 　　在上周，由于交易量非常大，发现part_max分区已经开始进入数据了，并且进入的数据量还不小，有大概3个partition的数据。担心大量数据进入part_max分区引起业务查询缓慢，于是决定实施split part_max分区，split执行的语句为：

ALTER table test01 SPliT PARTITION part_max AT(1000) INTO(PARTITION part_1000,PARTITION part_max);1100) (PARTITION part_1100,1); Font-weight: bold">1200) (PARTITION part_1200,1); Font-weight: bold">1300) INTO(PARTITION part_1300,PARTITION part_max);

 通过以上 *** 作，将part_max分区的数据分离到part_1000,part_1100,part_1200,part_1300里面，从而减小part_max数据量。

在执行 *** 作后，过了几分钟，业务方面出现了2个问题：

问题1：与该表相关的查询变得非常缓慢；

问题2：数据插入更新报出了大量的“ORA-01502”错误

（三）当时的解决方案

　　结合上次出现ORA-01502错误的经历，立马断定是索引出现问题了。查看索引，果然一部分新分区的局部分区索引失效了。立马删除索引，新建索引，将业务给启动起来。

　　现在回想起来，解决问题的方式略有不妥。出问题的表size非常的大，有150多GB，创建一个局部分区索引大概需要2.5小时，还好是一部分非关键业务，否则都不知道如何处理。

（四）查找原因&实验验证

回想了自己当天所做的 *** 作，仅仅对这些表进行了split。那么是不是split引起索引失效呢？我们通过实验验证一下。

STEP1：建测试表。创建sales表，以transactionID（交易ID）来分区

create table sales(    transactionID      number,goodsID            varchar2(30),saleTimekey        date,goodsdescrip       100)                           )partition by range(transactionID) (  partition part_100 values less than(200300400500600700800900values less than(maxvalue));

 

STEP2：创建主键约束和局部分区索引。

--6.1 创建主键约束，主键约束会引入唯一性索引alter table sales add constraint pk_sales_transactionID primary key(transactionID) using index local online tablespace users;6.2 创建普通的局部分区索引index lijiaman.goodsID on sales(goodsID) local online tablespace users;

 

STEP3：创建一个自增长序列。该序列用来模拟交易ID的自增长情况

create sequence sq_transactionID start with 1increment by maxvalue 100000000nocache;

 

STEP4：创建一个procedure，用来模拟数据插入

3.1 创建异常捕获表--该表用于捕获数据插入异常时的异常信息drop table sale_exception; sale_exception(  timekey   date,errcode   50));3.2创建插入sales表的pl/sql程序create or replace procedure p_sales is  v_sqlcode ;  v_sqlerrm 4000);begin  insert into sales    (transactionID,goodsID,goodsname,saleTimekey,goodsdescrip)      (sq_transactionID.Nextval,(select round(dbms_random.value(10000,100000000)) from dual),1)">select dbms_random.string('a',1); Font-weight: bold">25) 85)  dual));  commit;  exception     when others then      rollback;      v_sqlcode := sqlcode;      v_sqlerrm := substr(sqlerrm,1); Font-weight: bold">1,1)">);      into sale_exception (sysdate,v_sqlcode,v_sqlerrm);        ;end p_sales;

 

STEP5：创建job，定时向sales表插入数据。（多次执行，可以创建多个job向表里插入数据，这里我执行了10次，即由10个job每隔5s向sales表里面插入数据）

declarejob1 beginsys.dbms_job.submit(job => job1,what => p_sales;' sysdate,interval sysdate + 5/(1440*60));                --每隔5s向sales表插入一笔随机数据end;/

 

STEP6：查看sales表的数据信息。查看sales表的数据及各个分区的数据

count(*) from sales;                                    sales partition(part_100); sales partition(part_200); sales partition(part_300); sales partition(part_400); sales partition(part_500); sales partition(part_600); sales partition(part_700); sales partition(part_800); sales partition(part_900);from sales partition(part_max);                

 

STEP7：确认索引的状态

查看dba_indexes，发现index状态为N/A：

sql> select owner,table_name,index_name,uniqueness,status  dba_indexes i  2  where i.owner = liJIAMAN' and i.table_name SALES;OWNER                          table_name                     INDEX_name                     UNIQUEnesS STATUS---------------------------- ------------------------------ ------------------------------ ---------- --------liJIAMAN                       SALES                          PK_SALES_TRANSACTIONID         UNIQUE     N/AliJIAMAN                       SALES                          GOODSID                        NONUNIQUE  N/A

 

分区索引状态需要从dba_ind_partitions查看：

sqlselect index_owner,partition_name,1)"> dba_ind_partitions i  where  index_owner and index_name in(PK_SALES_TRANSACTIONIDGOODSID);INDEX_OWNER                    INDEX_name                     PARTITION_name                 STATUS---------------------------- ------------------------------ ------------------------------ --------liJIAMAN                       GOODSID                        PART_100                       USABLEliJIAMAN                       GOODSID                        PART_200                       USABLEliJIAMAN                       GOODSID                        PART_300                       USABLEliJIAMAN                       GOODSID                        PART_400                       USABLEliJIAMAN                       GOODSID                        PART_500                       USABLEliJIAMAN                       GOODSID                        PART_600                       USABLEliJIAMAN                       GOODSID                        PART_700                       USABLEliJIAMAN                       GOODSID                        PART_800                       USABLEliJIAMAN                       GOODSID                        PART_900                       USABLEliJIAMAN                       GOODSID                        PART_MAX                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_100                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_200                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_300                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_400                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_500                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_600                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_700                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_800                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_900                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_MAX                       USABLE20 rows selected

 通过最后的STATUS列，可以看到所有局部分区索引都是可用的。

 

STEP8：再次查看各分区的数据量

sqlfrom sales;          整个表有1244笔数据  COUNT(*)--------      1244sqlfrom sales partition(part_max);   part_max分区有375笔数据  --------       375

 

STEP9：执行split分区 *** 作

在上一步，max分区已经有375笔数据了，如果按照100大小作为一个分区，那么数据可以存放到4个分区里面。执行split分区 *** 作。

table sales split partition part_max at ( (partition part_1000,partition part_max); (partition part_1100,1)"> (partition part_1200,1)"> (partition part_1300,1); Font-weight: bold">1400)  (partition part_1400,1); Font-weight: bold">1500) into (partition part_1500,partition part_max);

 

STEP10：再次执行step7，查看分区索引的状态

sql dba_indexes i           /A

 

查看各个索引分区的状态：

14:44:42 sql dba_ind_partitions i           liJIAMAN                       GOODSID                        PART_100                       USABLEliJIAMAN                       GOODSID                        PART_1000                      UNUSABLEliJIAMAN                       GOODSID                        PART_1100                      UNUSABLEliJIAMAN                       GOODSID                        PART_1200                      UNUSABLEliJIAMAN                       GOODSID                        PART_1300                      UNUSABLEliJIAMAN                       GOODSID                        PART_1400                      UNUSABLEliJIAMAN                       GOODSID                        PART_1500                      USABLEliJIAMAN                       GOODSID                        PART_200                       USABLEliJIAMAN                       GOODSID                        PART_300                       USABLEliJIAMAN                       GOODSID                        PART_400                       USABLEliJIAMAN                       GOODSID                        PART_500                       USABLEliJIAMAN                       GOODSID                        PART_600                       USABLEliJIAMAN                       GOODSID                        PART_700                       USABLEliJIAMAN                       GOODSID                        PART_800                       USABLEliJIAMAN                       GOODSID                        PART_900                       USABLEliJIAMAN                       GOODSID                        PART_MAX                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_100                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_1000                      UNUSABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_1100                      UNUSABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_1200                      UNUSABLEINDEX_OWNER                    INDEX_name                     PARTITION_name                 STATUSliJIAMAN                       PK_SALES_TRANSACTIONID         PART_1300                      UNUSABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_1400                      UNUSABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_1500                      USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_200                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_300                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_400                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_500                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_600                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_700                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_800                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_900                       USABLEliJIAMAN                       PK_SALES_TRANSACTIONID         PART_MAX                       USABLE32 rows selected

从上面可以看到，2个索引的某些分区变为“UNUSABLE”状态，这些状态的索引都是新split出来的，但是并不包括全部，如part_1500分区的索引是可用的。上面的索引失效会引起2个问题：

问题1：在查询失效索引相关的分区时，由于索引不可用，查询速度会非常慢；

问题2：由于存在主键约束（带有唯一性索性），在失效索引相关的分区上，数据DML时会引发ORA-01502错误。我们可以从异常捕获表sales_exception查看异常信息：

这就明白了，为什么在split分区表后，生产系统中会出现以上2中情况。

小结：什么情况下split会引起index失效？

在测试时，发现在做split后，新split出来的分区，有的相关分区索引失效，而有的分区索引则不会失效。至于为什么会出现这种情况，个人认为是和segment的分裂有关，part_max段在split后，一个表segment分裂为多个，同样，对应的索引segment也分裂为多个。分裂后，如果一个index分区存放了所有分裂出来的数据，则索引分区与表分区依然可以对应；如果一个index分区存放不下所有数据，则会导致存在数据的索引分区与表分区数据对应不上，索引失效；如果是新分离出来的分区没有数据，则索引与表依然对应。

经过测试，发现规律：

1.part_max没有数据时，split *** 作不会引起local index失效；

2.part_max有数据：

    --split出来的第一个分区【可以存放】part_max里面的全部数据，split后part_max为空，则split 【不会】  引起索引失效；

    --split出来的第一个分区【不能够存放】part_max里面的数据，但是后续的分区可以存放下part_max的数据，split后part_max为空，split 【会】  引起索引失效。失效的索引为：新splits出来的有数据的分区，没有数据的分区不会失效，part_max同样不会失效；

    --split出来的全部分区【不能够存放】part_max里面的全部数据，split后part_max不为空，split 【会】  引起索引失效。失效的索引为：新split的全部索引和part_max；

                                                                                                 图3.split表分区索引失效梳理

 

（五）如何对应

 方案一：重建不可用的索引

sqlINDEX [schema.]index_name REBUILD PARTITION partition_name ONliNE];

我在出问题时重建了整个表的索引，没想到可以重建单个分区的索引。

方法小结：

优点：在部分分区的local index不可用后，使用该方法可以快速重建，快速恢复业务；

缺点：用到这种方法，说明部分local index已经不可用，业务已经出现上面2个问题。

 

方案二：在split时添加update indexes选项

sqltable ]table_name SPliT PARTITION partition_name AT (part_values) INTO (PARTITION part_values,PARTITION part_max) update indexes;

对于这种方法，个人最关心的问题是：

1.会不会导致local index失效；

2.如果不会导致locl index失效，在进行split时，是否存在锁，导致DML失败。

经过测试（测试表有2个分区，我们对其中一个分区进行split，该分区数据量有2GB，22800000行数据），发现在进行split时会产生TX锁，split持续了90s。在这期间DML *** 作hang住。查看local index的状态，未出现不可用的索引。

方法小结：

优点：不会造成local index不可用；

缺点：在执行 *** 作期间会造成锁表，如果表分区较大，持续时间将会很长，在生产中难以接受。

 

目前来看，对于7*24小时的系统，没有办法完美解决分区数据分离的问题，只有随时关注数据增长，尽量不要让数据进入part_max分区。接下来再找一找资料，争取对业务影响最小。

总结

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

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