首页 » 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 —

打赏

对不起,这篇文章暂时关闭评论。