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