文档解释
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);
