Oracle 19c分区表间数据迁移遇ORA-14702错误求解决及优化方案

一、当前脚本的问题分析 #

1. 核心错误原因 #

目标表STG_TGT为空，且自动分区仅在插入数据时才会创建对应分区，执行ALTER TABLE STG_TGT EXCHANGE PARTITION FOR (20240201)时，该分区不存在，直接触发ORA-14702错误。

2. 其他潜在问题 #

• 目标表创建脚本中DROP TABLE STG_TGT语句缺少分号，存在语法错误；
• 源表与目标表的主键列名不一致（STG_SRC_ID vs STG_TGT_ID），分区交换要求表结构完全匹配（列名、数据类型、约束等），后续会触发其他报错；
• 临时表STG_SRC_TMP的结构继承自源表，但目标表结构不同，交换时会因结构不匹配失败。

二、修复后的单分区交换脚本 #

先修正上述问题，实现单个分区的迁移：

-- 修复目标表创建脚本语法错误，统一表结构
DROP TABLE STG_TGT; -- 补充分号
CREATE TABLE STG_TGT
(
    STG_SRC_ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL, -- 与源表列名保持一致
    NM VARCHAR2(4000),
    BATCH_DT_ID NUMBER,
    CONSTRAINT PK_STG_TGT PRIMARY KEY (STG_SRC_ID)
);

ALTER TABLE STG_TGT MODIFY PARTITION BY LIST (BATCH_DT_ID) AUTOMATIC (PARTITION INITIAL_PARTITION VALUES (20240101)) ONLINE UPDATE INDEXES;

-- 先在目标表中创建要交换的分区（或插入一条临时数据触发自动分区创建）
ALTER TABLE STG_TGT ADD PARTITION P_20240201 VALUES (20240201);

-- 临时表创建（结构与源/目标表一致）
DROP TABLE STG_SRC_TMP;
CREATE TABLE STG_SRC_TMP FOR EXCHANGE WITH TABLE STG_SRC;

-- 源表与临时表交换分区
ALTER TABLE STG_SRC EXCHANGE PARTITION FOR (20240201) WITH TABLE STG_SRC_TMP INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES;

-- 临时表与目标表交换分区（此时目标表已存在对应分区）
ALTER TABLE STG_TGT EXCHANGE PARTITION FOR (20240201) WITH TABLE STG_SRC_TMP INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES;

-- 验证结果
SELECT COUNT(*) FROM STG_SRC WHERE BATCH_DT_ID = 20240201; -- 0
SELECT COUNT(*) FROM STG_TGT WHERE BATCH_DT_ID = 20240201; -- 1

三、适合数据仓库的批量迁移方案（保留7天数据） #

针对数据仓库“源表仅保留7天数据，其余迁移至目标表”的需求，推荐批量分区交换+自动清理的方案，效率远高于传统INSERT/DELETE：

1. 前提准备 #

• 确保源表与目标表结构完全一致（列名、数据类型、约束、分区策略）；
• 目标表开启自动分区（与源表一致），或提前创建待迁移分区；
• 源表的BATCH_DT_ID为日期转换的数字（如YYYYMMDD），便于计算过期日期。

2. 批量迁移脚本 #

DECLARE
    v_keep_date NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 7, 'YYYYMMDD')); -- 保留7天的起始日期
    CURSOR c_old_partitions IS
        SELECT PARTITION_NAME, HIGH_VALUE
        FROM USER_TAB_PARTITIONS
        WHERE TABLE_NAME = 'STG_SRC'
          AND TO_NUMBER(REPLACE(HIGH_VALUE, '''', '')) < v_keep_date; -- 筛选过期分区
    v_partition_val NUMBER;
BEGIN
    FOR rec IN c_old_partitions LOOP
        -- 解析分区对应的BATCH_DT_ID值
        v_partition_val := TO_NUMBER(REPLACE(rec.HIGH_VALUE, '''', ''));
        
        -- 1. 创建临时交换表（每次复用或重新创建）
        EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS STG_EXCH_TMP';
        EXECUTE IMMEDIATE 'CREATE TABLE STG_EXCH_TMP FOR EXCHANGE WITH TABLE STG_SRC';
        
        -- 2. 源表与临时表交换分区
        EXECUTE IMMEDIATE 'ALTER TABLE STG_SRC EXCHANGE PARTITION FOR (' || v_partition_val || ') ' ||
                          'WITH TABLE STG_EXCH_TMP INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES';
        
        -- 3. 确保目标表存在对应分区（自动分区可跳过，若未开启则手动创建）
        BEGIN
            EXECUTE IMMEDIATE 'ALTER TABLE STG_TGT EXCHANGE PARTITION FOR (' || v_partition_val || ') ' ||
                              'WITH TABLE STG_EXCH_TMP INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES';
        EXCEPTION
            WHEN OTHERS THEN
                -- 若分区不存在，先创建再交换
                EXECUTE IMMEDIATE 'ALTER TABLE STG_TGT ADD PARTITION P_' || v_partition_val || ' VALUES (' || v_partition_val || ')';
                EXECUTE IMMEDIATE 'ALTER TABLE STG_TGT EXCHANGE PARTITION FOR (' || v_partition_val || ') ' ||
                                  'WITH TABLE STG_EXCH_TMP INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES';
        END;
        
        -- 4. 可选：将目标表分区移动到指定表空间（满足不同表空间要求）
        EXECUTE IMMEDIATE 'ALTER TABLE STG_TGT MOVE PARTITION P_' || v_partition_val || ' TABLESPACE TGT_TABLESPACE';
        EXECUTE IMMEDIATE 'ALTER INDEX PK_STG_TGT REBUILD PARTITION P_' || v_partition_val || ' TABLESPACE TGT_INDEX_TS';
    END LOOP;
    
    COMMIT;
END;
/

3. 方案优势 #

• 高效：分区交换是元数据操作，几乎不涉及数据移动，亿级数据秒级完成；
• 低影响：源表仅在交换瞬间锁分区，对业务读写影响极小；
• 可扩展：支持批量处理所有过期分区，适合数据仓库定期清理场景；
• 满足表空间要求：通过MOVE PARTITION可将目标分区迁移到指定表空间。

四、注意事项 #

• 交换分区前需确保临时表无数据，避免数据丢失；
• 使用WITHOUT VALIDATION会跳过数据校验，若需保证分区数据正确性，可改为WITH VALIDATION（但会增加执行时间）；
• 全局索引需用UPDATE GLOBAL INDEXES避免失效，本地索引会随分区交换自动同步；
• 若源表有外键约束，需先禁用再交换，完成后重新启用。

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