ORA-14214: VALUES () cannot be used for Range subpartitioned tables

文档解释

ORA-14214: VALUES () cannot be used for Range subpartitioned tables

Cause: VALUES () clause was used for Range subpartitioned tables

Action: Use VALUES LESS THAN or AT clause with Range subpartitioned tables

ORA-14214: VALUES () 不能用于范围子分区表。

当在范围分区表中使用VALUES子句创建子分区时，将发出ORA-14214错误消息。这是因为VALUES子句仅用于列分区表，而不适用于范围分区表。

官方解释，如果尝试使用VALUES子句来创建范围子分区，则可能会引发ORA-14214错误消息。该错误消息表明VALUES子句不能用于范围子分区表：

CONN SYS AS SYSDBA

CREATE TABLE range_test (col1 DATE, col2 NUMBER)

PARTITION BY RANGE (col1)

SUBPARTITION BY RANGE (col2)

SUBPARTITIONS 4

(PARTITION p1 VALUES LESS THAN (TO_DATE(‘2004-01-05’, ‘YYYY-MM-DD’))

);

EXPECTED ERROR:

ORA-14214: VALUES () cannot be used for Range subpartitioned tables

常见案例

有时某些用户会尝试使用VALUES子句在范围分区表中创建子分区，正确的做法应该是使用Interval Partitioning。

正常处理方法及步骤

1、使用INTERVAL子句以INTERVAL格式创建Day子分区：

allow_rule_partition=TRUE

connect sys/oracle@test as sysdba

CREATE TABLE range_test

(

col1 DATE,

col2 NUMBER

)

PARTITION BY RANGE (col1)

SUBPARTITION BY RANGE (col2)

SUBPARTITIONS 4

(

PARTITION p1

INTERVAL (NUMTOYMINTERVAL(1,’DAY’))

(

SUBPARTITION p1_1 VALUES LESS THAN (100),

SUBPARTITION p1_2 VALUES LESS THAN (200),

SUBPARTITION p1_3 VALUES LESS THAN (300),

SUBPARTITION p1_4 VALUES LESS THAN (MAXVALUE)

)

STORE IN (dbs1, dbs2);

2、使用Interval子句以INTERVAL格式创建Month子分区：

（注意：此时 INTERVAL子句中NUMTOYMINTERVAL参数值改为1，’MONTH’即可）

CREATE TABLE range_test

(

col1 DATE,

col2 NUMBER

)

PARTITION BY RANGE (col1)

SUBPARTITION BY RANGE (col2)

SUBPARTITIONS 4

(

PARTITION p2

INTERVAL (NUMTOYMINTERVAL(1, ‘MONTH’))

(

SUBPARTITION p2_1 VALUES LESS THAN (100),

SUBPARTITION p2_2 VALUES LESS THAN (200),

SUBPARTITION p2_3 VALUES LESS THAN (300),

SUBPARTITION p2_4 VALUES LESS THAN (MAXVALUE)

)

STORE IN (dbs1, dbs2);