m6米乐安卓版下载-米乐app官网下载
暂无图片
7
暂无图片
暂无图片
暂无图片

oracle interval分区表报错ora-m6米乐安卓版下载

原创 柚子身上依 2023-08-18
585

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
image.png

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
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

网站地图