Oracle如何使用CTAS创建带子分区的表？ORA-00933报错如何解决？

报错核心原因 #

1. 语法顺序错误：Oracle使用CTAS创建分区表时，分区定义必须放在AS SELECT子句之前，你当前把查询逻辑写在了分区规则前面，不符合语法要求。
2. 日期格式错误：TO_DATE函数的格式参数写了5位年YYYYYMMDD，正确的4位年格式应为YYYYMMDD。
3. 结构缺失：所有分区配置需要放在PARTITION BY RANGE后的括号中，你当前写法缺少这层外层包裹。
4. 冗余符号：查询语句外的括号、列名外的双引号属于非必要冗余内容，列名加双引号会强制匹配大小写，容易引发额外报错。

正确实现代码 #

CREATE TABLE EMPLOYEE
PARTITION BY RANGE (START_DATE)
SUBPARTITION BY LIST(ID)
(
    PARTITION P1 VALUES LESS THAN (TO_DATE('20210920','YYYYMMDD')) (
        SUBPARTITION S1 VALUES (1),
        SUBPARTITION S2 VALUES (2),
        SUBPARTITION S3 VALUES (3)
    ),
    PARTITION P2 VALUES LESS THAN (TO_DATE('20210921','YYYYMMDD')) (
        SUBPARTITION S4 VALUES (1),
        SUBPARTITION S5 VALUES (2),
        SUBPARTITION S6 VALUES (3)
    ),
    PARTITION P3 VALUES LESS THAN (TO_DATE('20210922','YYYYMMDD')) (
        SUBPARTITION S7 VALUES (1),
        SUBPARTITION S8 VALUES (2),
        SUBPARTITION S9 VALUES (3)
    ),
    PARTITION P4 VALUES LESS THAN (MAXVALUE) (
        SUBPARTITION S10 VALUES (1),
        SUBPARTITION S11 VALUES (2),
        SUBPARTITION S12 VALUES (3)
    )
)
AS SELECT * FROM TASK;

优化方案 #

如果所有分区的子分区规则完全一致，可以使用子分区模板简化代码，无需逐个分区重复定义子分区：

CREATE TABLE EMPLOYEE
PARTITION BY RANGE (START_DATE)
SUBPARTITION BY LIST(ID)
SUBPARTITION TEMPLATE (
    SUBPARTITION S_1 VALUES (1),
    SUBPARTITION S_2 VALUES (2),
    SUBPARTITION S_3 VALUES (3)
)
(
    PARTITION P1 VALUES LESS THAN (TO_DATE('20210920','YYYYMMDD')),
    PARTITION P2 VALUES LESS THAN (TO_DATE('20210921','YYYYMMDD')),
    PARTITION P3 VALUES LESS THAN (TO_DATE('20210922','YYYYMMDD')),
    PARTITION P4 VALUES LESS THAN (MAXVALUE)
)
AS SELECT * FROM TASK;

执行后Oracle会自动为每个分区生成对应子分区，子分区命名规则为分区名_子分区模板名，无需手动指定。

内容的提问来源于stack exchange，提问作者merts97