Oracle创建分区表报错：ORA-14037: 分区 “PART_202108“ 的分区界限过高

最新推荐文章于 2023-09-19 14:40:16 发布

guang.yan

最新推荐文章于 2023-09-19 14:40:16 发布

阅读量1.5k

点赞数 1

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/zhongguootngxu/article/details/124661352

版权

本文讲述了在创建分区表时遇到的ORA-14037错误，原因在于WORK_DATE为VARCHAR导致分区界限过高。解决方案包括将WORK_DATE字段类型改为DATE，并提供两种修正后的SQL语句。同时，建议使用日期类型自动分区以降低维护成本。

摘要由CSDN通过智能技术生成

创建分区表sql语句如下：

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null,
tel VARCHAR2(12) not null,
WORK_DATE VARCHAR2(15)
) tablespace test
partition by range(WORK_DATE)
(
partition part_2020 values less than (to_date('2021-01-01','YYYY-MM-DD')),
partition part_202101 values less than (to_date('2021-02-01','YYYY-MM-DD')),
partition part_202102 values less than (to_date('2021-03-01','YYYY-MM-DD')),
partition part_202103 values less than (to_date('2021-04-01','YYYY-MM-DD')),
partition part_202104 values less than (to_date('2021-05-01','YYYY-MM-DD')),
partition part_202105 values less than (to_date('2021-06-01','YYYY-MM-DD')),
partition part_202106 values less than (to_date('2021-07-01','YYYY-MM-DD')),
partition part_202107 values less than (to_date('2021-08-01','YYYY-MM-DD')),
partition part_202108 values less than (to_date('2021-09-01','YYYY-MM-DD')),
partition part_202109 values less than (to_date('2021-10-01','YYYY-MM-DD')),
partition part_202110 values less than (to_date('2021-11-01','YYYY-MM-DD')),
partition part_202111 values less than (to_date('2021-12-01','YYYY-MM-DD')),
partition part_202112 values less than (to_date('2022-01-01','YYYY-MM-DD')),
partition part_default values less than (maxvalue)
);

执行报错ORA-14037: 分区 “PART_202108” 的分区界限过高

解决办法：
1.范围分区的WORK_DATE为VARCHAR2类型而非DATA类型，改为DATA类型的字段就没有问题了
2.或者更改sql语句如下

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null,
tel VARCHAR2(12) not null,
WORK_DATE VARCHAR2(15)
) tablespace test
partition by range(WORK_DATE)
(
partition PART_2020 values less than ('20210101'),
partition PART_202101 values less than ('20210201'),
partition PART_202102 values less than ('20210301'),
partition PART_202103 values less than ('20210401'),
partition PART_202104 values less than ('20210501'),
partition PART_202105 values less than ('20210601'),
partition PART_202106 values less than ('20210701'),
partition PART_202107 values less than ('20210801'),
partition PART_202108 values less than ('20210901'),
partition PART_202109 values less than ('20211001'),
partition PART_202110 values less than ('20211101'),
partition PART_202111 values less than ('20211201'),
partition PART_202112 values less than ('20220101'),
partition PART_DEFAULT  values less than (MAXVALUE)
);