Oracle间隔分区(interval分区)的分区字段无法为NULL值

Oracle间隔分区(interval分区)的分区字段无法为NULL值

前言

年前在对测试大表做在线重定义的实验的时候，对时间字段做分区字段并且为interval分区时，发现报错。

后来发现这是因为interval分区的分区字段无法为NULL值。

实验演示

如下为脚本：

create table interval_tab
(
 id number not null,
 name varchar2(20),
 create_time date
 )
 PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
 (    partition part_t01 values less than(to_date('2020-01', 'yyyy-mm')));

insert into interval_tab values (1,'aa',to_date('2020-05-01','yyyy-mm-dd'));
insert into interval_tab values (2,'bb',to_date('2021-01-01','yyyy-mm-dd'));
insert into interval_tab values (3,'cc',to_date('2020-07-23','yyyy-mm-dd'));
insert into interval_tab values (4,'dd',null);
commit;

执行信息如下，当插入NULL值后报ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions。

16:54:42 ZKM@zkm(997)> create table interval_tab
16:54:42   2  (
16:54:42   3   id number not null,
16:54:42   4   name varchar2(20),
16:54:42   5   create_time date
16:54:42   6   )
16:54:42   7   PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
16:54:42   8   (  partition part_t01 values less than(to_date('2020-01', 'yyyy-mm')));

Table created.

Elapsed: 00:00:00.02

16:58:01 ZKM@zkm(997)> col partition_name for a25
16:58:09 ZKM@zkm(997)> col high_value for a100
16:58:17 ZKM@zkm(997)> set line 500
16:58:24 ZKM@zkm(997)> select PARTITION_NAME,HIGH_VALUE,INTERVAL from dba_tab_partitions where table_owner='ZKM' and table_name='INTERVAL_TAB';

PARTITION_NAME            HIGH_VALUE                                                                                           INTERVAL
------------------------- ---------------------------------------------------------------------------------------------------- ---------
PART_T01                  TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                  NO

Elapsed: 00:00:00.00
16:59:00 ZKM@zkm(997)> insert into interval_tab values (1,'aa',to_date('2020-05-01','yyyy-mm-dd'));

1 row created.

Elapsed: 00:00:00.08
16:59:01 ZKM@zkm(997)> insert into interval_tab values (2,'bb',to_date('2021-01-01','yyyy-mm-dd'));

1 row created.

Elapsed: 00:00:00.02
16:59:09 ZKM@zkm(997)> insert into interval_tab values (3,'cc',to_date('2020-07-23','yyyy-mm-dd'));

1 row created.

Elapsed: 00:00:00.02
17:00:10 ZKM@zkm(997)> select PARTITION_NAME,HIGH_VALUE,INTERVAL from dba_tab_partitions where table_owner='ZKM' and table_name='INTERVAL_TAB';

PARTITION_NAME            HIGH_VALUE                                                                                           INTERVAL
------------------------- ---------------------------------------------------------------------------------------------------- ---------
PART_T01                  TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                  NO
SYS_P3361                 TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                  YES
SYS_P3362                 TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                  YES
SYS_P3363                 TO_DATE(' 2020-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                  YES

Elapsed: 00:00:00.00
17:00:14 ZKM@zkm(997)> commit;

Commit complete.

Elapsed: 00:00:00.01

17:00:28 ZKM@zkm(997)> insert into interval_tab values (4,'dd',null);
insert into interval_tab values (4,'dd',null)
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions


Elapsed: 00:00:00.01

其他

如何将已有的interval分区禁用，通过如下方式：

ALTER TABLE "SCHEMA"."T1" SET INTERVAL ();

一旦为表关闭了间隔分区，现有的已经创建的分区将进一步用作范围分区。

之后可以自己手工分区或创建定时任务无自动分区。

参考

Insert Fails With ORA-14300 On Partition Table (Doc ID 1472941.1)