如何编写Oracle批量迁移数据的存储过程（每批5万条）

针对4亿条记录的大表迁移需求，以下是Oracle环境下的批量迁移存储过程实现，每次处理5万条数据，避免一次性操作占用过多系统资源：

基础版（复制数据，保留源表） #

该版本仅将数据复制到目标表，源表数据保持完整：

CREATE OR REPLACE PROCEDURE BATCH_MIGRATE_DATA
IS
  v_batch_size CONSTANT NUMBER := 50000; -- 每次批量处理条数
  v_rows_processed NUMBER;
BEGIN
  v_rows_processed := v_batch_size; -- 初始化循环条件
  
  WHILE v_rows_processed = v_batch_size LOOP
    -- 批量插入数据到目标表
    INSERT INTO TABLE_2 (NAME, TAX_ID, PROD_CODE)
    SELECT NAME, TAX_ID, PROD_CODE
    FROM (
      -- 内层查询先获取数据，再通过ROWNUM限制条数
      SELECT t.*, ROWNUM rn
      FROM TABLE_1 t
      -- 在此添加数据筛选条件（如需要迁移部分数据）
      -- WHERE t.CREATE_DATE >= TO_DATE('20230101', 'YYYYMMDD')
    )
    WHERE rn <= v_batch_size;
    
    -- 获取本次实际处理的行数
    v_rows_processed := SQL%ROWCOUNT;
    
    -- 提交事务，释放UNDO空间并避免锁等待
    COMMIT;
    
    -- 可选：添加1秒延迟，降低系统瞬时负载（根据实际情况调整）
    DBMS_LOCK.SLEEP(1);
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('迁移完成，累计处理记录数：' || (v_batch_size * FLOOR((v_rows_processed - 1)/v_batch_size) + v_rows_processed));
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('迁移失败：' || SQLERRM);
    RAISE; -- 抛出异常以便上层处理
END;
/

进阶版（带迁移标记，支持重复执行） #

如果需要避免重复迁移、支持中断后继续执行，可以给源表添加IS_MIGRATED CHAR(1) DEFAULT 'N'字段，通过标记控制迁移范围：

CREATE OR REPLACE PROCEDURE BATCH_MIGRATE_DATA_WITH_MARK
IS
  v_batch_size CONSTANT NUMBER := 50000;
  v_rows_processed NUMBER;
BEGIN
  LOOP
    -- 锁定待迁移的记录，避免并发冲突
    UPDATE TABLE_1
    SET IS_MIGRATED = 'L' -- L=锁定状态
    WHERE ROWID IN (
      SELECT ROWID
      FROM TABLE_1
      WHERE IS_MIGRATED = 'N'
        -- 添加你的数据筛选条件
        -- AND t.TAX_ID IS NOT NULL
      AND ROWNUM <= v_batch_size
    );
    
    v_rows_processed := SQL%ROWCOUNT;
    
    -- 没有待迁移数据则退出循环
    IF v_rows_processed = 0 THEN
      COMMIT;
      EXIT;
    END IF;
    
    -- 将锁定的记录插入目标表
    INSERT INTO TABLE_2 (NAME, TAX_ID, PROD_CODE)
    SELECT NAME, TAX_ID, PROD_CODE
    FROM TABLE_1
    WHERE IS_MIGRATED = 'L';
    
    -- 将锁定标记更新为已迁移
    UPDATE TABLE_1
    SET IS_MIGRATED = 'Y' -- Y=已迁移状态
    WHERE IS_MIGRATED = 'L';
    
    COMMIT;
    DBMS_LOCK.SLEEP(1);
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('迁移完成，累计处理记录数：' || v_rows_processed);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('迁移失败：' || SQLERRM);
    RAISE;
END;
/

关键说明 #

• 批量大小控制：v_batch_size设置为5万，可根据数据库IO、内存资源调整，IO性能优异的系统可适当调大。
• 事务提交策略：每次批量操作后立即提交，避免大事务导致UNDO表空间耗尽、锁等待时间过长。
• ROWNUM正确用法：必须在内层查询先获取全量符合条件的数据，再通过ROWNUM限制条数，否则会导致分页逻辑错误。
• 与SQL Server方案差异：Oracle无TOP和OUTPUT语法，通过ROWNUM实现批量取数；本方案默认保留源表数据，若需删除源数据，可在插入后添加DELETE FROM TABLE_1 WHERE IS_MIGRATED = 'Y'语句。

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