首页 » ORACLE 9i-23ai » 通过最大分区数据错误看Oracle 23ai 错误提示友好增强
通过最大分区数据错误看Oracle 23ai 错误提示友好增强
之前在写《Oracle 23c 几个开发相关新特性》有简单记录在报错上的友好增强，最近oracle发布了23版本命名为从C(cloud)系转向ai系，通过官方下载了个vmbox磁盘镜像分分钟运行起了oracle 23c free ，今天刚好公司内部讨论一个interval partition分区个数上限的问题，我们看看23ai版本的提示有多明确。
Parttiion分区是oracle数据库对于海量数据的常用技术，为了减少分区维护工作量对于range类型分区增加了interval分区类型，固定步长式按需创建，但是注意同样有最大分区数限制，截至到23ai版本单表无论是否多级分区(subparititon)最大分区数依旧是1024k-1(1048575)个.interval分区最大上限就是最小分区+分区粒度*分区个数，如果是subpartition 使用了template模板或hash 个数那每个partition也会预留固定个数的subpartition，分区个数需要/每分区subpartiton个数.
下面创建个例子
create table anbob.hash_by_interval (
int_dt date not null,
num_val number(10,0) not null,
strng_dat varchar2(100)
)
partition by range (int_dt) interval (numtodsinterval(1,'DAY'))
subpartition by hash (num_val) subpartitions 1024
(
partition ip0 values less than (to_date('2010-01-01','YYYY-MM-DD'))
)
;
SQL> select to_date('2010-01-01','YYYY-MM-DD')+1023;
TO_DATE('2010-01-
-----------------
20121020 00:00:00
-- oracle 11g
SQL> SQL> insert into anbob.hash_by_interval values (to_date('2012-10-20','YYYY-MM-DD'), 1, rpad('x',100));
insert into anbob.hash_by_interval values (to_date('2012-10-20','YYYY-MM-DD'), 1, rpad('x',100))
*
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted
number of partitions
SQL> insert into anbob.hash_by_interval values (to_date('2012-10-19','YYYY-MM-DD'), 1, rpad('x',100));
insert into anbob.hash_by_interval values (to_date('2012-10-19','YYYY-MM-DD'), 1, rpad('x',100))
*
ERROR at line 1:
ORA-14299: total number of partitions/subpartitions exceeds the maximum limit
-- oracle 23 ai
SQL> insert into anbob.hash_by_interval values (to_date('2012-10-20','YYYY-MM-DD'), 1, rpad('x',100));
insert into anbob.hash_by_interval values (to_date('2012-10-20','YYYY-MM-DD'), 1, rpad('x',100))
*
ERROR at line 1:
ORA-14300: partitioning key ["2012-10-20T00:00:00", 1] is beyond the calculated
exclusive upper interval bound "2012-10-19T00:00:00" for table
ANBOB.HASH_BY_INTERVAL
Help: https://docs.oracle.com/error-help/db/ora-14300/
SQL> insert into anbob.hash_by_interval values (to_date('2012-10-19','YYYY-MM-DD'), 1, rpad('x',100));
insert into anbob.hash_by_interval values (to_date('2012-10-19','YYYY-MM-DD'), 1, rpad('x',100))
*
ERROR at line 1:
ORA-14299: total number of partitions/subpartitions exceeds the maximum limit
Help: https://docs.oracle.com/error-help/db/ora-14299/
SQL> insert into anbob.hash_by_interval values (to_date('2012-10-18','YYYY-MM-DD'), 1, rpad('x',100));
1 row created.
Note:
从表面上看，这些错误似乎在说同样的事情，但事实并非如此。回到逻辑限制，最大分区数是 1048575；第一个插入错误报告分区键将映射到最大分区限制之外的分区，这将创建编号为 1048576 的分区，这显然违反了逻辑限制。第二个插入错误报告它应该进入分区号 1025，这并不违反单个分区的子分区数量，但它创建总共 1048576 个子分区，确实违反了子分区总数限制。它们的条件不同，因此会产生不同的错误。这两种情况都会导致同样的问题：分区表中没有当天的数据。
而且23ai版本的错误提示比11g更加明确，提示出了当前表的interval bound的上限是19号，所以20号超出分区，19号的子分区又刚好超出个数限制，同时还给出了oracle help网站的链接。想必客户看到这个错误就不会再问该问题，interval是即使中间分区未创建，最大上限也会预留,这也是dba_part_tables的PARTITION_COUNT对于interval直接是1048575.
SQL> SELECT TABLE_NAME,PARTITIONING_TYPE,INTERVAL,PARTITION_COUNT from dba_part_tables where owner='ANBOB' TABLE_NAME PARTITIONING_TYPE INTERVAL PARTITION_COUNT ------------------------------------------------------------ ------------------ ------------------------------ --------------- HASH_BY_INTERVAL RANGE NUMTODSINTERVAL(1,'DAY') 1048575 [oracle@localhost tpt-oracle-master]$ ora SQL*Plus: Release 23.0.0.0.0 - Production on Tue May 7 11:21:26 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL> @init SQL> @printtab "select * from dba_tab_partitions where table_name=''HASH_BY_INTERVAL''"; TABLE_OWNER : ANBOB TABLE_NAME : HASH_BY_INTERVAL COMPOSITE : YES PARTITION_NAME : IP0 SUBPARTITION_COUNT : 1024 HIGH_VALUE : TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') HIGH_VALUE_LENGTH : 83 PARTITION_POSITION : 1 TABLESPACE_NAME : USERS PCT_FREE : 10 PCT_USED : INI_TRANS : 1 MAX_TRANS : 255 INITIAL_EXTENT : NEXT_EXTENT : MIN_EXTENT : MAX_EXTENT : MAX_SIZE : PCT_INCREASE : FREELISTS : FREELIST_GROUPS : LOGGING : NONE COMPRESSION : NONE COMPRESS_FOR : NUM_ROWS : BLOCKS : EMPTY_BLOCKS : AVG_SPACE : CHAIN_CNT : AVG_ROW_LEN : SAMPLE_SIZE : LAST_ANALYZED : BUFFER_POOL : DEFAULT FLASH_CACHE : DEFAULT CELL_FLASH_CACHE : DEFAULT GLOBAL_STATS : NO USER_STATS : NO IS_NESTED : N/A PARENT_TABLE_PARTITION : N/A INTERVAL : NO SEGMENT_CREATED : NONE INDEXING : NONE READ_ONLY : NONE INMEMORY : NONE INMEMORY_PRIORITY : INMEMORY_DISTRIBUTE : INMEMORY_COMPRESSION : INMEMORY_DUPLICATE : CELLMEMORY : INMEMORY_SERVICE : INMEMORY_SERVICE_NAME : MEMOPTIMIZE_READ : N/A MEMOPTIMIZE_WRITE : N/A HIGH_VALUE_CLOB : TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') HIGH_VALUE_JSON : {"high_value":"2010-01-01T00:00:00"} SHARDED_TABLE_CHUNK_ID : ----------------- TABLE_OWNER : ANBOB TABLE_NAME : HASH_BY_INTERVAL COMPOSITE : YES PARTITION_NAME : SYS_P3498 SUBPARTITION_COUNT : 1024 HIGH_VALUE : TO_DATE(' 2012-10-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') HIGH_VALUE_LENGTH : 83 PARTITION_POSITION : 2 TABLESPACE_NAME : USERS PCT_FREE : 10 PCT_USED : INI_TRANS : 1 MAX_TRANS : 255 INITIAL_EXTENT : NEXT_EXTENT : MIN_EXTENT : MAX_EXTENT : MAX_SIZE : PCT_INCREASE : FREELISTS : FREELIST_GROUPS : LOGGING : NONE COMPRESSION : NONE COMPRESS_FOR : NUM_ROWS : BLOCKS : EMPTY_BLOCKS : AVG_SPACE : CHAIN_CNT : AVG_ROW_LEN : SAMPLE_SIZE : LAST_ANALYZED : BUFFER_POOL : DEFAULT FLASH_CACHE : DEFAULT CELL_FLASH_CACHE : DEFAULT GLOBAL_STATS : NO USER_STATS : NO IS_NESTED : N/A PARENT_TABLE_PARTITION : N/A INTERVAL : YES SEGMENT_CREATED : NONE INDEXING : NONE READ_ONLY : NONE INMEMORY : NONE INMEMORY_PRIORITY : INMEMORY_DISTRIBUTE : INMEMORY_COMPRESSION : INMEMORY_DUPLICATE : CELLMEMORY : INMEMORY_SERVICE : INMEMORY_SERVICE_NAME : MEMOPTIMIZE_READ : N/A MEMOPTIMIZE_WRITE : N/A HIGH_VALUE_CLOB : TO_DATE(' 2012-10-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') HIGH_VALUE_JSON : {"high_value":"2012-10-19T00:00:00"} SHARDED_TABLE_CHUNK_ID : ----------------- PL/SQL procedure successfully completed. SQL> desc dba_tab_partitions Name Null? Type ------------------------------- -------- ---------------------------- 1 TABLE_OWNER VARCHAR2(128) 2 TABLE_NAME VARCHAR2(128) 3 COMPOSITE VARCHAR2(3) 4 PARTITION_NAME VARCHAR2(128) 5 SUBPARTITION_COUNT NUMBER 6 HIGH_VALUE LONG 7 HIGH_VALUE_LENGTH NUMBER 8 PARTITION_POSITION NUMBER 9 TABLESPACE_NAME VARCHAR2(30) 10 PCT_FREE NUMBER 11 PCT_USED NUMBER 12 INI_TRANS NUMBER 13 MAX_TRANS NUMBER 14 INITIAL_EXTENT NUMBER 15 NEXT_EXTENT NUMBER 16 MIN_EXTENT NUMBER 17 MAX_EXTENT NUMBER 18 MAX_SIZE NUMBER 19 PCT_INCREASE NUMBER 20 FREELISTS NUMBER 21 FREELIST_GROUPS NUMBER 22 LOGGING VARCHAR2(7) 23 COMPRESSION VARCHAR2(8) 24 COMPRESS_FOR VARCHAR2(30) 25 NUM_ROWS NUMBER 26 BLOCKS NUMBER 27 EMPTY_BLOCKS NUMBER 28 AVG_SPACE NUMBER 29 CHAIN_CNT NUMBER 30 AVG_ROW_LEN NUMBER 31 SAMPLE_SIZE NUMBER 32 LAST_ANALYZED DATE 33 BUFFER_POOL VARCHAR2(7) 34 FLASH_CACHE VARCHAR2(7) 35 CELL_FLASH_CACHE VARCHAR2(7) 36 GLOBAL_STATS VARCHAR2(3) 37 USER_STATS VARCHAR2(3) 38 IS_NESTED VARCHAR2(3) 39 PARENT_TABLE_PARTITION VARCHAR2(128) 40 INTERVAL VARCHAR2(3) 41 SEGMENT_CREATED VARCHAR2(4) 42 INDEXING VARCHAR2(4) 43 READ_ONLY VARCHAR2(4) 44 INMEMORY VARCHAR2(8) 45 INMEMORY_PRIORITY VARCHAR2(8) 46 INMEMORY_DISTRIBUTE VARCHAR2(15) 47 INMEMORY_COMPRESSION VARCHAR2(17) 48 INMEMORY_DUPLICATE VARCHAR2(13) 49 CELLMEMORY VARCHAR2(24) 50 INMEMORY_SERVICE VARCHAR2(12) 51 INMEMORY_SERVICE_NAME VARCHAR2(1000) 52 MEMOPTIMIZE_READ VARCHAR2(8) 53 MEMOPTIMIZE_WRITE VARCHAR2(8) 54 HIGH_VALUE_CLOB CLOB 55 HIGH_VALUE_JSON JSON 56 SHARDED_TABLE_CHUNK_ID NUMBER
Note:
注意从dba_tab_partitions的high_value_xxx 列可以看到分区上限，同时过去HIGH_VALUE是long类型不方便计算，从23ai版本增加了2个字段clob和json，可以更方便筛选。
对于错误的友好性，这也是国产数据库学习的方向。
— enjoy —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。