適用范圍

Oracle 11g及以上版本

問題概述

interval 分區(qū)表插入數(shù)據(jù)報(bào)錯(cuò)如下：

SQL> insert into interval_number_table01 values(1111119000010,'a',sysdate); 
insert into interval_number_table01 values(1111119000010,'a',sysdate)
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted
number of partitions

問題原因

報(bào)錯(cuò)原因查看，分區(qū)數(shù)超過 1048575 報(bào)這個(gè)錯(cuò)誤

SQL> !oerr ora 14300
14300, 00000, "partitioning key maps to a partition outside maximum permitted number of partitions"
// *Cause:  The row inserted had a partitioning key that maps to a partition number greater than 1048575
// *Action  Ensure that the partitioning key falls within 1048575 partitions or subpartitions.

1.查看分區(qū)信息

測(cè)試數(shù)據(jù)

CREATE TABLE interval_number_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20),
  birthday            DATE    
)
PARTITION BY RANGE(employee_id)
INTERVAL (500000)
(
  PARTITION partition10 VALUES LESS THAN(10)
);

insert into interval_number_table01 values(10,'a',sysdate);
insert into interval_number_table01 values(600000,'a',sysdate);
insert into interval_number_table01 values(1200000,'a',sysdate);
insert into interval_number_table01 values(1111119000010,'a',sysdate); 

SQL> insert into interval_number_table01 values(1111119000010,'a',sysdate); 
insert into interval_number_table01 values(1111119000010,'a',sysdate)
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted
number of partitions

該表分區(qū)和子分區(qū)數(shù)只有 4 個(gè)，按說沒有超過 1048575 不應(yīng)該報(bào)錯(cuò)。

SQL> set line 999
SQL> col TABLE_NAME for a25
SQL> col HIGH_VALUE for a25
SQL> col PARTITION_NAME for a20
SQL> select table_name,partition_position,high_value,num_rows ,PARTITION_NAME ,INTERVAL from dba_tab_partitions where table_name='INTERVAL_NUMBER_TABLE01'

TABLE_NAME                PARTITION_POSITION HIGH_VALUE                  NUM_ROWS PARTITION_NAME       INTERV
------------------------- ------------------ ------------------------- ---------- -------------------- ------
INTERVAL_NUMBER_TABLE01                    1 10                                   PARTITION10          NO
INTERVAL_NUMBER_TABLE01                    2 500010                               SYS_P59981           YES
INTERVAL_NUMBER_TABLE01                    3 1000010                              SYS_P59982           YES
INTERVAL_NUMBER_TABLE01                    4 1500010                              SYS_P59983           YES

SQL> select table_name,high_value,num_rows ,PARTITION_NAME ,INTERVAL from dba_tab_subpartitions where table_name='INTERVAL_NUMBER_TABLE01';

no rows selected

SQL> 

2.查看記錄的分區(qū)數(shù)

顯示此表分區(qū)數(shù)已經(jīng)達(dá)到 1048575 個(gè)，然后interval 值是 500000 。
官方文檔說明，interval分區(qū) PARTITION_COUNT 值始終是 1048575

SQL> select PARTITION_COUNT,DEF_SUBPARTITION_COUNT,SUBPARTITIONING_KEY_COUNT,PARTITIONING_KEY_COUNT,INTERVAL from dba_part_tables where table_name='INTERVAL_NUMBER_TABLE01';

PARTITION_COUNT DEF_SUBPARTITION_COUNT SUBPARTITIONING_KEY_COUNT PARTITIONING_KEY_COUNT INTERVAL
--------------- ---------------------- ------------------------- ---------------------- ----------
        1048575                      0                         0                      1 500000

3.interval分區(qū)表可以插入的最大值是多少？

3.1.查看建表語句

interval 分區(qū)表，使用DBMS_METADATA.GET_DDL 查看表結(jié)果時(shí)不顯示自動(dòng)創(chuàng)建的分區(qū)，只顯示初始創(chuàng)建時(shí)的分區(qū)。

SQL> set long 9999
SQL> set pages 999
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'INTERVAL_NUMBER_TABLE01')  FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','INTERVAL_NUMBER_TABLE01')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."INTERVAL_NUMBER_TABLE01"
   (    "EMPLOYEE_ID" NUMBER,
        "EMPLOYEE_NAME" VARCHAR2(20),
        "BIRTHDAY" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
  PARTITION BY RANGE ("EMPLOYEE_ID") INTERVAL (500000)
 (PARTITION "PARTITION10"  VALUES LESS THAN (10)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" )

3.2.計(jì)算可以插入的最大值

dba_tab_partitions 表中interval字段是YES是自動(dòng)創(chuàng)建的interval分區(qū)，NO表示不是interval分區(qū)。

分區(qū)數(shù)計(jì)算公式 = (最大interval分區(qū) HIGH_VALUE - 最大非interval分區(qū) HIGH_VALUE) / interval + 非interval分區(qū)數(shù) = 1048575

最大非interval分區(qū) HIGH_VALUE ： 10
interval值           ：  500000
非interval分區(qū)數(shù)    ：  1
最大interval分區(qū) HIGH_VALUE = (1048575 - 1)*500000 + 10 = 524287000010 

通過上面的計(jì)算可以得知分區(qū)最大的HIGH VALUE 是 524287000010，那么可以插入的最大值只能是 524287000010 - 1 = 524287000009 。下面進(jìn)行驗(yàn)證:

SQL> insert into interval_number_table01 values(524287000009,'a',sysdate); 

1 row created.

SQL> insert into interval_number_table01 values(524287000010,'a',sysdate); 
insert into interval_number_table01 values(524287000010,'a',sysdate)
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions
SQL> 

所以最上面的 insert into interval_number_table01 values(1111119000010,‘a(chǎn)’,sysdate); 插入的值肯定會(huì)報(bào)錯(cuò)，因?yàn)椴迦氲淖畲笾档讲涣?1111119000010 。

解決方案

如果想這條SQL執(zhí)行成功，有下面兩種方式

1.修改interval值為更大的值，比如修改成 5000000

SQL>  alter table INTERVAL_NUMBER_TABLE01 set  interval(5000000);

Table altered.

SQL> insert into interval_number_table01 values(1111119000010,'a',sysdate); 

1 row created.

SQL> select table_name,partition_position,high_value,num_rows ,PARTITION_NAME ,INTERVAL from dba_tab_partitions where table_name='INTERVAL_NUMBER_TABLE01';

TABLE_NAME                PARTITION_POSITION HIGH_VALUE                  NUM_ROWS PARTITION_NAME       INTERV
------------------------- ------------------ ------------------------- ---------- -------------------- ------
INTERVAL_NUMBER_TABLE01                    1 10                                   PARTITION10          NO
INTERVAL_NUMBER_TABLE01                    2 500010                               SYS_P59986           NO
INTERVAL_NUMBER_TABLE01                    3 1000010                              SYS_P59987           NO
INTERVAL_NUMBER_TABLE01                    4 1500010                              SYS_P59988           NO
INTERVAL_NUMBER_TABLE01                    5 1111121500010                        SYS_P59989           YES

SQL> select PARTITION_COUNT,DEF_SUBPARTITION_COUNT,SUBPARTITIONING_KEY_COUNT,PARTITIONING_KEY_COUNT,INTERVAL from dba_part_tables where table_name='INTERVAL_NUMBER_TABLE01'

PARTITION_COUNT DEF_SUBPARTITION_COUNT SUBPARTITIONING_KEY_COUNT PARTITIONING_KEY_COUNT INTERVAL
--------------- ---------------------- ------------------------- ---------------------- --------------------
        1048575                      0                         0                      1 5000000

2.轉(zhuǎn)換interval分區(qū)為range分區(qū)

不輸入interval() 括號(hào)中的值既可以轉(zhuǎn)換為interval分區(qū)。

SQL> alter table INTERVAL_NUMBER_TABLE01 set  interval();

Table altered.

SQL> select table_name,partition_position,high_value,num_rows ,PARTITION_NAME ,INTERVAL from dba_tab_partitions where table_name='INTERVAL_NUMBER_TABLE01';

TABLE_NAME                PARTITION_POSITION HIGH_VALUE                  NUM_ROWS PARTITION_NAME       INTERVAL
------------------------- ------------------ ------------------------- ---------- -------------------- --------------------
INTERVAL_NUMBER_TABLE01                    1 10                                   PARTITION10          NO
INTERVAL_NUMBER_TABLE01                    2 500010                               SYS_P59986           NO
INTERVAL_NUMBER_TABLE01                    3 1000010                              SYS_P59987           NO
INTERVAL_NUMBER_TABLE01                    4 1500010                              SYS_P59988           NO

SQL> select PARTITION_COUNT,DEF_SUBPARTITION_COUNT,SUBPARTITIONING_KEY_COUNT,PARTITIONING_KEY_COUNT,INTERVAL from dba_part_tables where table_name='INTERVAL_NUMBER_TABLE01';

PARTITION_COUNT DEF_SUBPARTITION_COUNT SUBPARTITIONING_KEY_COUNT PARTITIONING_KEY_COUNT INTERVAL
--------------- ---------------------- ------------------------- ---------------------- --------------------
              4                      0                         0                      1

SQL> ALTER TABLE INTERVAL_NUMBER_TABLE01 ADD PARTITION PAR10000 VALUES less than (2111119000010);

Table altered.

SQL> select table_name,partition_position,high_value,num_rows ,PARTITION_NAME ,INTERVAL from dba_tab_partitions where table_name='INTERVAL_NUMBER_TABLE01';

TABLE_NAME                PARTITION_POSITION HIGH_VALUE                  NUM_ROWS PARTITION_NAME       INTERVAL
------------------------- ------------------ ------------------------- ---------- -------------------- --------------------
INTERVAL_NUMBER_TABLE01                    1 10                                   PARTITION10          NO
INTERVAL_NUMBER_TABLE01                    2 500010                               SYS_P59986           NO
INTERVAL_NUMBER_TABLE01                    3 1000010                              SYS_P59987           NO
INTERVAL_NUMBER_TABLE01                    4 1500010                              SYS_P59988           NO
INTERVAL_NUMBER_TABLE01                    5 2111119000010                        PAR10000             NO

SQL> insert into interval_number_table01 values(1111119000010,'a',sysdate); 

1 row created.

SQL> 

備注說明

1. interval 調(diào)整為 5000000 時(shí)可以插入的最大值計(jì)算：

分區(qū)數(shù)計(jì)算公式 = (最大分區(qū) HIGH_VALUE - 最大非interval分區(qū) HIGH_VALUE) / interval + 4 = 1048575

最大非interval分區(qū) HIGH_VALUE ： 1500010
interval值           ：  5000000
非interval分區(qū)數(shù)    ：  4
最大分區(qū) HIGH_VALUE = (1048575 - 4)*5000000 + 1500010 = 5242856500010

通過上面的計(jì)算可以得知分區(qū)最大的HiGH VALUE 是 5242855000010，那么可以插入的最大值只能是 5242855000010 - 1 = 5242855000009 。下面進(jìn)行驗(yàn)證:

SQL> insert into interval_number_table01 values(5242856500009,'a',sysdate);

1 row created.

SQL> insert into interval_number_table01 values(5242856500010,'a',sysdate);
insert into interval_number_table01 values(5242856500010,'a',sysdate)
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

2. interval()不等于interval(null)

設(shè)置為interval()會(huì)報(bào)錯(cuò)。

SQL> alter table INTERVAL_NUMBER_TABLE01 set  interval(null);
alter table INTERVAL_NUMBER_TABLE01 set  interval(null)
*
ERROR at line 1:
ORA-14752: Interval expression is not a constant of the correct type

參考文檔

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=i7uqzn6lx_4&_afrLoop=432142251415294
https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/ALL_PART_TABLES.html#GUID-F6EC7236-9A38-44FA-9A17-F118D1E4F64D
https://support.enmotech.com/article/639/search
https://support.enmotech.com/article/652/search
https://support.enmotech.com/article/654/search
https://support.enmotech.com/article/640/search