Oracle split分区表引起ORA-01502错误

时间:2022-06-29 21:45:07

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

(一)目的

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

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

Oracle split分区表引起ORA-01502错误

                                                    图1.表栏位信息

 Oracle split分区表引起ORA-01502错误

                                                              图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查看异常信息:

Oracle split分区表引起ORA-01502错误

这就明白了,为什么在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;

Oracle split分区表引起ORA-01502错误

                                                                                                 图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分区。接下来再找一找资料,争取对业务影响最小。