ORA-14037 :解决办法

ORA-14037 :解决办法

ORA-14037: 分区 "P2" 的分区界限过高

 CREATE TABLE PROVINCE_INFO
(
  PROVINCE_ID VARCHAR2(8) NOT NULL,
  PROVINCE_NAME VARCHAR2(40) NOT NULL,
  CREATE_DT DATE
)
PARTITION BY RANGE (PROVINCE_ID)(
  PARTITION P1 VALUES LESS THAN ('8'),
  PARTITION P2 VALUES LESS THAN ('9'),
  PARTITION P3 VALUES LESS THAN ('10'),
  PARTITION P4 VALUES LESS THAN ('11'),
  PARTITION P5 VALUES LESS THAN (MAXVALUE)
);

查Oracle 错误文档 ORA-14037

ORA-14037 partition bound of partition "string" is too high
Cause: High bound of the partition whose name (explicitly specified by the
user) is displayed in this message did not collate lower than that of the
following partition, which is illegal.
Action: Ensure that high bound of every partition (except for the last one)
collates lower than that of a following partition.

问题原因：是分区p2的分区界限比P3的大了,按通常的理解 9 比10小，但是oracle比较字符串时，会首先从第一个字符比较。因为"9">"1"，所以会认为"9"<"10",所以就出错了。

解决办法：

create table PROVINCE_INFO
(
  PROVINCE_ID varchar2(8) not null,
  PROVINCE_NAME varchar2(40) not null,
  CREATE_DT DATE
)
partition by range (PROVINCE_ID)
(
PARTITION p1 VALUES LESS THAN ('08'),
PARTITION p2 VALUES LESS THAN ('09'),
PARTITION p3 VALUES LESS THAN ('10'),
PARTITION p4 VALUES LESS THAN ('11'),
PARTITION p5 VALUES LESS THAN (maxvalue)
);