Oracle 11g分区表拆分报错ORA-00906的技术求助

错误原因 #

你遇到的ORA-00906错误，核心是Oracle 11g中拆分范围-列表复合分区的语法结构使用错误。你错误地将VALUES LESS THAN (2028)放在了SPLIT PARTITION子句之后，而正确语法要求，这个范围边界定义必须嵌套在INTO子句的新分区描述中。

正确操作方式 #

方式一：一次性拆分并定义子分区 #

使用该语句可以直接拆分出P2027分区并同时创建对应的子分区，完全匹配原表的子分区结构：

ALTER TABLE "OWNER"."PARTITIONEDTABLE"
SPLIT PARTITION P_OTHER
INTO (
    PARTITION P2027 VALUES LESS THAN (2028) (
        SUBPARTITION "P2027_SP0" VALUES (0),
        SUBPARTITION "P2027_SP1" VALUES (1),
        SUBPARTITION "P2027_SP2" VALUES (2),
        SUBPARTITION "P2027_SP3" VALUES (3),
        SUBPARTITION "P2027_SP4" VALUES (4),
        SUBPARTITION "P2027_SP5" VALUES (5),
        SUBPARTITION "P2027_SP6" VALUES (6),
        SUBPARTITION "P2027_SP7" VALUES (7),
        SUBPARTITION "P2027_SP8" VALUES (8),
        SUBPARTITION "P2027_SP9" VALUES (9),
        SUBPARTITION "P2027_SP_OTHER" VALUES (DEFAULT)
    ),
    PARTITION P_OTHER VALUES LESS THAN (MAXVALUE) (
        SUBPARTITION "P_OTHER_SP0" VALUES (0),
        SUBPARTITION "P_OTHER_SP1" VALUES (1),
        SUBPARTITION "P_OTHER_SP2" VALUES (2),
        SUBPARTITION "P_OTHER_SP3" VALUES (3),
        SUBPARTITION "P_OTHER_SP4" VALUES (4),
        SUBPARTITION "P_OTHER_SP5" VALUES (5),
        SUBPARTITION "P_OTHER_SP6" VALUES (6),
        SUBPARTITION "P_OTHER_SP7" VALUES (7),
        SUBPARTITION "P_OTHER_SP8" VALUES (8),
        SUBPARTITION "P_OTHER_SP9" VALUES (9),
        SUBPARTITION "P_OTHER_SP_OTHER" VALUES (DEFAULT)
    )
);

方式二：分步拆分（先拆分区，后加子分区） #

如果希望先完成父分区拆分，再单独添加子分区，可按以下步骤执行：

1. 拆分父分区：

ALTER TABLE "OWNER"."PARTITIONEDTABLE"
SPLIT PARTITION P_OTHER
INTO (
    PARTITION P2027 VALUES LESS THAN (2028),
    PARTITION P_OTHER VALUES LESS THAN (MAXVALUE)
);

2. 为P2027分区逐个添加子分区：

ALTER TABLE "OWNER"."PARTITIONEDTABLE" MODIFY PARTITION P2027 ADD SUBPARTITION "P2027_SP0" VALUES (0);
ALTER TABLE "OWNER"."PARTITIONEDTABLE" MODIFY PARTITION P2027 ADD SUBPARTITION "P2027_SP1" VALUES (1);
ALTER TABLE "OWNER"."PARTITIONEDTABLE" MODIFY PARTITION P2027 ADD SUBPARTITION "P2027_SP2" VALUES (2);
ALTER TABLE "OWNER"."PARTITIONEDTABLE" MODIFY PARTITION P2027 ADD SUBPARTITION "P2027_SP3" VALUES (3);
ALTER TABLE "OWNER"."PARTITIONEDTABLE" MODIFY PARTITION P2027 ADD SUBPARTITION "P2027_SP4" VALUES (4);
ALTER TABLE "OWNER"."PARTITIONEDTABLE" MODIFY PARTITION P2027 ADD SUBPARTITION "P2027_SP5" VALUES (5);
ALTER TABLE "OWNER"."PARTITIONEDTABLE" MODIFY PARTITION P2027 ADD SUBPARTITION "P2027_SP6" VALUES (6);
ALTER TABLE "OWNER"."PARTITIONEDTABLE" MODIFY PARTITION P2027 ADD SUBPARTITION "P2027_SP7" VALUES (7);
ALTER TABLE "OWNER"."PARTITIONEDTABLE" MODIFY PARTITION P2027 ADD SUBPARTITION "P2027_SP8" VALUES (8);
ALTER TABLE "OWNER"."PARTITIONEDTABLE" MODIFY PARTITION P2027 ADD SUBPARTITION "P2027_SP9" VALUES (9);
ALTER TABLE "OWNER"."PARTITIONEDTABLE" MODIFY PARTITION P2027 ADD SUBPARTITION "P2027_SP_OTHER" VALUES (DEFAULT);

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