如何为Oracle现有范围-间隔分区表新增主分区？

不需要重建表，直接通过ALTER TABLE语句即可完成主分区新增和子分区模板更新，以下是具体操作步骤：

1. 更新子分区模板（适配未来年份） #

先扩展子分区模板，新增未来年份的子分区定义，确保新创建的主分区自动包含这些子分区：

ALTER TABLE VER MODIFY SUBPARTITION TEMPLATE (
    SUBPARTITION Y2024 VALUES LESS THAN (TO_DATE('2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
    SUBPARTITION Y2025 VALUES LESS THAN (TO_DATE('2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
    SUBPARTITION Y2026 VALUES LESS THAN (TO_DATE('2026-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
    SUBPARTITION Future VALUES LESS THAN (MAXVALUE)
);

注意：修改模板仅影响新创建的主分区，现有主分区的子分区需单独调整（如需）。

2. 手动新增主分区 #

原表已配置interval (1)，理论上插入数据时会自动创建对应Vmo_til_version的主分区，但可提前手动批量创建，避免插入时的分区创建开销：

单个主分区创建 #

ALTER TABLE VER ADD PARTITION
    PARTITION Ver_100000 VALUES LESS THAN (100001)
    (
        SUBPARTITION Ver_100000_Y2024 VALUES LESS THAN (TO_DATE('2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
        SUBPARTITION Ver_100000_Y2025 VALUES LESS THAN (TO_DATE('2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
        SUBPARTITION Ver_100000_Future VALUES LESS THAN (MAXVALUE)
    );

批量创建主分区（PL/SQL循环） #

如果需要一次性创建多个主分区（比如100001到100100），用PL/SQL循环实现：

BEGIN
    FOR i IN 100001..100100 LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE VER ADD PARTITION
            PARTITION Ver_' || i || ' VALUES LESS THAN (' || (i+1) || ')
            (
                SUBPARTITION Ver_' || i || '_Y2024 VALUES LESS THAN (TO_DATE(''2024-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                SUBPARTITION Ver_' || i || '_Y2025 VALUES LESS THAN (TO_DATE(''2025-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')),
                SUBPARTITION Ver_' || i || '_Future VALUES LESS THAN (MAXVALUE)
            )';
    END LOOP;
END;
/

3. 验证分区创建结果 #

查询主分区：

SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name = 'VER' ORDER BY high_value;

查询子分区：

SELECT partition_name, subpartition_name, high_value FROM user_tab_subpartitions WHERE table_name = 'VER' ORDER BY partition_name, high_value;

4. 可选：迁移现有maxvalue子分区数据 #

如果现有Future子分区堆积了大量历史数据（如2024年后的数据），可新增对应子分区并迁移数据（需停机窗口）：

-- 为现有主分区新增Y2025子分区
ALTER TABLE VER MODIFY PARTITION [现有主分区名] ADD SUBPARTITION Y2025 VALUES LESS THAN (TO_DATE('2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));

-- 移动指定范围数据到新子分区
ALTER TABLE VER MOVE SUBPARTITION [现有主分区名]_FUTURE TO SUBPARTITION [现有主分区名]_Y2025 
WHERE Vmo_enddate >= TO_DATE('2024-01-01', 'YYYY-MM-DD') 
  AND Vmo_enddate < TO_DATE('2025-01-01', 'YYYY-MM-DD');

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