概述
继上次删除分区表的分区遇到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); ALTER TABLE test01 SPLIT PARTITION part_max AT(1100) INTO(PARTITION part_1100,PARTITION part_max); ALTER TABLE test01 SPLIT PARTITION part_max AT(1200) INTO(PARTITION part_1200,PARTITION part_max); ALTER TABLE test01 SPLIT PARTITION part_max AT(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 number, goodsName varchar2(30), saleTimekey date, goodsdescrip varchar2(100) ) partition by range(transactionId) ( partition part_100 values less than(100), partition part_200 values less than(200), partition part_300 values less than(300), partition part_400 values less than(400), partition part_500 values less than(500), partition part_600 values less than(600), partition part_700 values less than(700), partition part_800 values less than(800), partition part_900 values less than(900), partition part_max values 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 创建普通的局部分区索引 create index lijiaman.goodsId on sales(goodsId) local online tablespace users;
STEP3：创建一个自增长序列。该序列用来模拟交易ID的自增长情况
create sequence sq_transactionId start with 1 increment by 1 maxvalue 100000000 nocache;
STEP4：创建一个procedure，用来模拟数据插入
--3.1 创建异常捕获表 --该表用于捕获数据插入异常时的异常信息 --drop table sale_exception; create table sale_exception ( timekey date, errcode varchar2(50), errmess varchar2(500) ); --3.2创建插入sales表的pl/sql程序 create or replace procedure p_sales is v_sqlcode number; v_sqlerrm varchar2(4000); begin insert into sales (transactionId, goodsId, goodsName, saleTimekey, goodsdescrip) values (sq_transactionId.Nextval, (select round(dbms_random.value(10000, 100000000)) from dual), (select dbms_random.string('a', 25) from dual), sysdate, (select dbms_random.string('a', 85) from dual)); commit; exception when others then rollback; v_sqlcode := sqlcode; v_sqlerrm := substr(sqlerrm,1,100); insert into sale_exception values(sysdate,v_sqlcode,v_sqlerrm); commit; end p_sales;
STEP5：创建job，定时向sales表插入数据。（多次执行，可以创建多个job向表里插入数据，这里我执行了10次，即由10个job每隔5s向sales表里面插入数据）
declare job1 number; begin sys.dbms_job.submit(job => job1, what => 'p_sales;', next_date => sysdate, interval => 'sysdate + 5/(1440*60)'); --每隔5s向sales表插入一笔随机数据 commit; end; /
STEP6：查看sales表的数据信息。查看sales表的数据及各个分区的数据
select count(*) from sales; select count(*) from sales partition(part_100); select count(*) from sales partition(part_200); select count(*) from sales partition(part_300); select count(*) from sales partition(part_400); select count(*) from sales partition(part_500); select count(*) from sales partition(part_600); select count(*) from sales partition(part_700); select count(*) from sales partition(part_800); select count(*) from sales partition(part_900); select count(*) from sales partition(part_max);
STEP7：确认索引的状态
查看dba_indexes，发现index状态为N/A：
SQL> select owner,table_name,index_name,uniqueness,status from 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/A LIJIAMAN SALES GOODSID NONUNIQUE N/A
分区索引状态需要从dba_ind_partitions查看：
SQL> select index_owner,index_name,partition_name,status from dba_ind_partitions i 2 where index_owner = 'LIJIAMAN' and index_name in('PK_SALES_TRANSACTIONID','GOODSID'); INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ ------------------------------ -------- LIJIAMAN GOODSID PART_100 USABLE LIJIAMAN GOODSID PART_200 USABLE LIJIAMAN GOODSID PART_300 USABLE LIJIAMAN GOODSID PART_400 USABLE LIJIAMAN GOODSID PART_500 USABLE LIJIAMAN GOODSID PART_600 USABLE LIJIAMAN GOODSID PART_700 USABLE LIJIAMAN GOODSID PART_800 USABLE LIJIAMAN GOODSID PART_900 USABLE LIJIAMAN GOODSID PART_MAX USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_100 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_200 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_300 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_400 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_500 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_600 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_700 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_800 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_900 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_MAX USABLE 20 rows selected
通过最后的STATUS列，可以看到所有局部分区索引都是可用的。
STEP8：再次查看各分区的数据量
SQL> select count(*) from sales; --整个表有1244笔数据 COUNT(*) ---------- 1244 SQL> select count(*) from sales partition(part_max); --part_max分区有375笔数据 COUNT(*) ---------- 375
STEP9：执行split分区操作
在上一步，max分区已经有375笔数据了，如果按照100大小作为一个分区，那么数据可以存放到4个分区里面。执行split分区操作。
alter table sales split partition part_max at (1000) into (partition part_1000,partition part_max); alter table sales split partition part_max at (1100) into (partition part_1100,partition part_max); alter table sales split partition part_max at (1200) into (partition part_1200,partition part_max); alter table sales split partition part_max at (1300) into (partition part_1300,partition part_max); alter table sales split partition part_max at (1400) into (partition part_1400,partition part_max); alter table sales split partition part_max at (1500) into (partition part_1500,partition part_max);
STEP10：再次执行step7，查看分区索引的状态
SQL> select owner,table_name,index_name,uniqueness,status from 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/A LIJIAMAN SALES GOODSID NONUNIQUE N/A
查看各个索引分区的状态：
14:44:42 SQL> select index_owner,index_name,partition_name,status from dba_ind_partitions i 2 where index_owner = 'LIJIAMAN' and index_name in('PK_SALES_TRANSACTIONID','GOODSID'); INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ ------------------------------ -------- LIJIAMAN GOODSID PART_100 USABLE LIJIAMAN GOODSID PART_1000 UNUSABLE LIJIAMAN GOODSID PART_1100 UNUSABLE LIJIAMAN GOODSID PART_1200 UNUSABLE LIJIAMAN GOODSID PART_1300 UNUSABLE LIJIAMAN GOODSID PART_1400 UNUSABLE LIJIAMAN GOODSID PART_1500 USABLE LIJIAMAN GOODSID PART_200 USABLE LIJIAMAN GOODSID PART_300 USABLE LIJIAMAN GOODSID PART_400 USABLE LIJIAMAN GOODSID PART_500 USABLE LIJIAMAN GOODSID PART_600 USABLE LIJIAMAN GOODSID PART_700 USABLE LIJIAMAN GOODSID PART_800 USABLE LIJIAMAN GOODSID PART_900 USABLE LIJIAMAN GOODSID PART_MAX USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_100 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_1000 UNUSABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_1100 UNUSABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_1200 UNUSABLE INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS ------------------------------ ------------------------------ ------------------------------ -------- LIJIAMAN PK_SALES_TRANSACTIONID PART_1300 UNUSABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_1400 UNUSABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_1500 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_200 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_300 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_400 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_500 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_600 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_700 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_800 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_900 USABLE LIJIAMAN PK_SALES_TRANSACTIONID PART_MAX USABLE 32 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表分区索引失效梳理
（五）如何对应
方案一：重建不可用的索引
SQL> ALTER INDEX [schema.]index_name REBUILD PARTITION partition_name [ONLINE];
我在出问题时重建了整个表的索引，没想到可以重建单个分区的索引。
方法小结：
优点：在部分分区的local index不可用后，使用该方法可以快速重建，快速恢复业务；
缺点：用到这种方法，说明部分local index已经不可用，业务已经出现上面2个问题。
方案二：在split时添加update indexes选项
SQL> ALTER TABLE [schema.]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 split分区表引起ORA-01502错误所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
![[Oracle] ORA-01502 错误](/uploads/reation/bcimg6.png)
发表评论 取消回复