适用范围
Oracle 11g及以上版本
问题概述
interval 分区表插入数据报错如下：
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
问题原因
报错原因查看，分区数超过 1048575 报这个错误
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.查看分区信息
测试数据
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
该表分区和子分区数只有 4 个，按说没有超过 1048575 不应该报错。
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.查看记录的分区数
显示此表分区数已经达到 1048575 个，然后interval 值是 500000 。
官方文档说明，interval分区 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分区表可以插入的最大值是多少？
3.1.查看建表语句
interval 分区表，使用DBMS_METADATA.GET_DDL 查看表结果时不显示自动创建的分区，只显示初始创建时的分区。
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.计算可以插入的最大值
dba_tab_partitions 表中interval字段是YES是自动创建的interval分区，NO表示不是interval分区。
分区数计算公式 = (最大interval分区 HIGH_VALUE - 最大非interval分区 HIGH_VALUE) / interval + 非interval分区数 = 1048575
最大非interval分区 HIGH_VALUE ： 10
interval值 ： 500000
非interval分区数 ： 1
最大interval分区 HIGH_VALUE = (1048575 - 1)*500000 + 10 = 524287000010
通过上面的计算可以得知分区最大的HIGH VALUE 是 524287000010，那么可以插入的最大值只能是 524287000010 - 1 = 524287000009 。下面进行验证:
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’,sysdate); 插入的值肯定会报错，因为插入的最大值到不了 1111119000010 。
解决方案
如果想这条SQL执行成功，有下面两种方式
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.转换interval分区为range分区
不输入interval() 括号中的值既可以转换为interval分区。
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 调整为 5000000 时可以插入的最大值计算：
分区数计算公式 = (最大分区 HIGH_VALUE - 最大非interval分区 HIGH_VALUE) / interval + 4 = 1048575
最大非interval分区 HIGH_VALUE ： 1500010
interval值 ： 5000000
非interval分区数 ： 4
最大分区 HIGH_VALUE = (1048575 - 4)*5000000 + 1500010 = 5242856500010
通过上面的计算可以得知分区最大的HiGH VALUE 是 5242855000010，那么可以插入的最大值只能是 5242855000010 - 1 = 5242855000009 。下面进行验证:
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)
设置为interval()会报错。
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
最后修改时间：2023-08-19 15:13:06
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
