1. 为什么存储过程依然是批量迁移的硬核选择历史数据表迁移这件事做过一次的人都知道它有多磨人。表面上看就是把A库的数据搬到B库但真正动手之后你会发现数据量、字段映射、业务规则、停机窗口、回滚方案每一项都能让你加班到凌晨。我前后参与过七八次不同规模的历史数据迁移从几百万行的小表到几十亿行的大表都碰过最后发现一个规律越是批量、越是规则复杂、越要求可重复执行的场景基于数据库存储过程的方案就越稳。这篇文章要聊的就是基于Oracle存储过程的历史数据表迁移方案。核心思路是把迁移逻辑全部下沉到数据库层用PL/SQL编写可重复调用、可断点续传、可分批提交的迁移过程。它解决的核心问题是在有限的停机窗口内把历史表数据准确、高效、可追溯地搬到目标表同时保证业务规则在迁移过程中被正确执行。适合谁看适合手里有Oracle环境、需要做数据搬迁或数据归档的开发和运维人员也适合正在设计迁移方案、纠结用ETL工具还是手写脚本的技术负责人。为什么不用现成的ETL工具这个问题我被问过很多次。ETL工具当然有它的优势图形化、可视化、上手快但到了历史数据迁移这种场景它有几个绕不开的短板。第一业务规则复杂的时候图形化配置反而比写SQL更费劲一个多层嵌套的条件判断在工具里拖拽半天写成PL/SQL可能就十几行。第二性能调优的空间有限ETL工具底层怎么执行你控制不了而存储过程你可以精确控制游标、批量提交、并行度。第三可移植性和可审计性存储过程就是一段代码可以进版本库、可以做代码审查、可以随时重跑出了问题定位也直接。当然这不是说ETL工具不好而是说在Oracle环境内做表到表的迁移存储过程是更贴近底层、更可控的选择。我个人的经验是迁移方案的设计重点不在于“能不能搬过去”而在于“搬得对不对、快不快、出问题能不能兜住”。这三个问题存储过程方案都能给出比较扎实的答案。下面我会从整体设计思路、核心细节、实操过程、问题排查几个维度把整套方案拆开讲清楚尽量让不同基础的人都能照着落地。2. 迁移方案的整体设计与思路拆解2.1 先想清楚迁移的四种典型场景在动手写任何代码之前必须先明确这次迁移属于哪种场景因为不同场景对应的策略完全不同。我一般把历史数据迁移分成四类场景类型典型特征推荐策略全量一次性迁移数据量中等停机窗口充足直接INSERT SELECT分批提交全量分批迁移数据量大窗口有限按主键或时间分区多批次循环增量追加迁移历史表持续有新数据写入时间戳/序列号增量抽取归档式迁移老数据搬到历史库主库瘦身迁移后删除源数据需严格校验大部分项目其实是“全量分批迁移”加上“归档式迁移”的组合。比如把三年前的历史订单从生产库搬到历史库既要分批搬搬完还要清理源表。场景判断错了后面所有设计都是白费。我见过一个项目明明是增量场景结果按全量写了个一次性脚本上线第二天新数据就漏了返工重来。2.2 存储过程方案的分层结构整套方案我习惯分成四层每层职责清晰互不干扰控制层负责调度、批次管理、断点记录、异常捕获。核心是一张迁移日志表记录每一批次的开始时间、结束时间、处理行数、状态。抽取层负责从源表按规则读取数据。用游标或者分页查询避免一次性把几千万行读进内存。转换层负责字段映射、类型转换、业务规则计算。这一层是业务逻辑最集中的地方也是bug最容易藏身的地方。装载层负责写入目标表配合批量提交和异常处理。为什么要分层因为迁移过程中最怕的就是“一锅粥”所有逻辑写在一个大过程里出了问题根本不知道是哪一步。分层之后每一层可以单独测试、单独优化、单独替换。比如抽取层从游标改成并行查询不影响转换和装载逻辑。2.3 批次划分策略的选择逻辑批次划分是性能的关键。划得太粗单批数据量大回滚段压力大、锁竞争严重划得太细批次数量多调度开销和日志写入开销上来了。我的经验值是单批次控制在5000到20000行之间具体看单行数据宽度和业务复杂度。划分维度一般有三种按主键区间适合主键连续且分布均匀的表。比如ID从1到1000万每批1万行分成1000批。按时间字段适合有时间维度的表比如按月份、按天划分。好处是业务含义清晰坏处是数据分布可能不均。按ROWID或分区适合超大表直接按物理存储划分效率最高但可读性差。提示批次划分字段必须有索引否则每批查询都要全表扫描性能会惨不忍睹。这一点我在早期项目里踩过坑源表按创建时间分批但没建索引第一批跑了几十分钟加上索引之后降到几秒。2.4 断点续传与幂等性设计迁移最怕跑到一半失败然后从头再来。所以断点续传是必须的。实现方式很简单在迁移日志表里记录每个批次的完成状态过程启动时先查日志跳过已完成的批次从第一个未完成的批次继续。幂等性同样重要。所谓幂等就是同一批数据重复执行不会产生重复记录或错误结果。实现手段有两种一是迁移前先删除目标表中该批次对应的数据再插入二是用MERGE语句做插入或更新。我一般推荐第一种逻辑简单、可控性强删除加插入在一个事务里要么全成功要么全回滚。这两点设计好了整个迁移过程就从“一次性赌博”变成了“可反复执行的安全操作”心理压力小很多。3. 核心细节解析与实操要点3.1 源表与目标表的字段映射处理字段映射看起来简单实际上是最容易出问题的地方。常见的情况有字段名变了、类型变了、长度变了、精度变了、必填变可选了。我一般会先做一张映射对照表把源字段、目标字段、转换规则、备注全部列清楚然后再写代码。类型转换要特别注意几个坑NUMBER精度问题源表是NUMBER(10,2)目标表是NUMBER(12,4)直接插入没问题但反过来就会截断。截断不会报错只会静默丢精度非常危险。DATE和TIMESTAMPOracle的DATE只到秒TIMESTAMP可以到小数秒。从TIMESTAMP转到DATE会丢精度需要业务确认是否可接受。VARCHAR2长度源表VARCHAR2(500)目标表VARCHAR2(200)超长会直接报错。迁移前必须用MAX(LENGTH())检查实际最大长度。字符集问题源库和目标库字符集不一致时中文可能变问号。这个必须在迁移前确认。-- 检查源表各字符字段的实际最大长度 SELECT MAX(LENGTH(col1)) AS max_col1, MAX(LENGTH(col2)) AS max_col2 FROM source_table;3.2 业务规则的转换实现业务规则是迁移的灵魂。历史数据往往带着旧系统的业务逻辑搬到新系统后需要按新规则重新计算。比如订单状态码从旧编码映射到新编码金额字段需要按新税率重新计算客户等级根据历史消费重新评定空值需要填充默认值这些规则我建议全部写成独立的映射函数而不是散落在主过程里。好处是规则可以单独测试改规则不用动主流程。比如写一个fn_map_order_status函数输入旧状态码返回新状态码主过程直接调用。CREATE OR REPLACE FUNCTION fn_map_order_status(p_old_status VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN CASE p_old_status WHEN 01 THEN PENDING WHEN 02 THEN PAID WHEN 03 THEN SHIPPED WHEN 04 THEN COMPLETED WHEN 99 THEN CANCELLED ELSE UNKNOWN END; END;注意映射函数里一定要有ELSE分支兜底否则遇到未预期的旧值会返回NULL导致目标表数据缺失。我见过因为漏了一个状态码几千条订单状态变成空的案例。3.3 批量提交与回滚段管理批量提交的节奏直接影响性能和稳定性。提交太频繁日志同步开销大提交太少回滚段膨胀undo表空间可能撑爆。我的经验是每5000到10000行提交一次同时监控undo表空间使用率。-- 批量提交的核心逻辑 IF MOD(v_count, 5000) 0 THEN COMMIT; END IF;另外要注意提交之后游标状态会变化如果用的是FOR UPDATE游标提交后继续FETCH可能报错。所以迁移场景一般用普通游标不加FOR UPDATE避免这个问题。回滚段管理还有一个技巧迁移前把大事务拆小每个批次独立提交这样即使某一批失败也只回滚这一批不影响前面已完成的批次。这就是断点续传的基础。3.4 性能优化的几个关键点存储过程迁移的性能瓶颈通常在三处源表读取、转换计算、目标表写入。对应的优化手段源表读取确保批次划分字段有索引必要时用并行查询PARALLEL hint。但并行度不要开太高一般CPU核数的一半比较稳妥。转换计算避免在循环里做重复计算能提前算好的常量提前算。避免在WHERE条件里对字段做函数运算会导致索引失效。目标表写入迁移前禁用目标表的触发器和约束迁移后再启用并校验可以大幅提升插入速度。用APPEND hint走直接路径插入绕过buffer cache。-- 直接路径插入示例 INSERT /* APPEND */ INTO target_table (col1, col2) SELECT col1, col2 FROM source_table WHERE ...;提示APPEND插入会锁表迁移期间目标表不可用所以只适合停机迁移场景。如果目标表还在被业务访问不能用这个方式。3.5 迁移日志表的设计日志表是整个方案的眼睛。没有日志迁移就是黑盒。我设计的日志表一般包含这些字段字段名类型说明batch_idNUMBER批次编号batch_rangeVARCHAR2(200)批次范围描述start_timeDATE批次开始时间end_timeDATE批次结束时间row_countNUMBER处理行数statusVARCHAR2(20)RUNNING/SUCCESS/FAILEDerror_msgVARCHAR2(4000)错误信息有了这张表迁移进度一目了然失败批次也能快速定位。我还会加一个retry_count字段记录重试次数防止某个批次无限重试。4. 实操过程与核心环节实现4.1 迁移前的准备工作清单动手写代码之前这些准备工作必须做完否则后面一定返工数据量摸底统计源表总行数、各批次预估行数、单行平均宽度。数据质量检查检查主键重复、必填字段为空、外键悬空、异常值。目标表结构确认字段类型、长度、约束、索引、触发器全部核对。空间评估目标表空间、undo表空间、临时表空间是否充足。备份策略迁移前对源表和目标表做备份这是最后的保命手段。回滚方案明确失败后怎么回退是删目标表数据还是整表恢复。数据质量检查的SQL示例-- 检查主键重复 SELECT pk_col, COUNT(*) FROM source_table GROUP BY pk_col HAVING COUNT(*) 1; -- 检查必填字段为空 SELECT COUNT(*) FROM source_table WHERE required_col IS NULL; -- 检查异常值 SELECT COUNT(*) FROM source_table WHERE amount 0;4.2 迁移主过程的完整实现下面是一个完整的迁移主过程框架我把它拆成几个关键部分讲解。CREATE OR REPLACE PROCEDURE prc_migrate_history_data( p_batch_size IN NUMBER DEFAULT 10000, p_max_retry IN NUMBER DEFAULT 3 ) IS v_batch_id NUMBER; v_start_id NUMBER; v_end_id NUMBER; v_max_id NUMBER; v_min_id NUMBER; v_row_count NUMBER : 0; v_total_count NUMBER : 0; v_status VARCHAR2(20); BEGIN -- 获取源表主键范围 SELECT MIN(id), MAX(id) INTO v_min_id, v_max_id FROM source_table; -- 循环处理每个批次 v_start_id : v_min_id; WHILE v_start_id v_max_id LOOP v_end_id : v_start_id p_batch_size - 1; -- 检查该批次是否已完成 SELECT COUNT(*) INTO v_row_count FROM migration_log WHERE batch_range v_start_id || - || v_end_id AND status SUCCESS; IF v_row_count 0 THEN -- 记录批次开始 INSERT INTO migration_log(batch_id, batch_range, start_time, status) VALUES(seq_batch_id.NEXTVAL, v_start_id || - || v_end_id, SYSDATE, RUNNING); COMMIT; BEGIN -- 执行迁移 INSERT INTO target_table(col1, col2, col3) SELECT col1, fn_map_order_status(col2), NVL(col3, 0) FROM source_table WHERE id BETWEEN v_start_id AND v_end_id; v_row_count : SQL%ROWCOUNT; -- 更新日志为成功 UPDATE migration_log SET end_time SYSDATE, row_count v_row_count, status SUCCESS WHERE batch_range v_start_id || - || v_end_id AND status RUNNING; COMMIT; v_total_count : v_total_count v_row_count; EXCEPTION WHEN OTHERS THEN ROLLBACK; UPDATE migration_log SET end_time SYSDATE, status FAILED, error_msg SQLERRM WHERE batch_range v_start_id || - || v_end_id AND status RUNNING; COMMIT; RAISE; END; END IF; v_start_id : v_end_id 1; END LOOP; DBMS_OUTPUT.PUT_LINE(迁移完成总行数 || v_total_count); END;这个框架有几个关键设计点值得说明第一批次范围用主键区间表示简单直观日志表里一眼能看出跑到哪了。第二每个批次独立事务成功就提交失败就回滚并记录不影响其他批次。第三异常处理里先ROLLBACK再更新日志保证日志状态和实际数据一致。4.3 分批提交的参数计算过程批次大小到底设多少这不是拍脑袋决定的需要算一下。假设单行数据平均宽度500字节目标表有3个索引undo表空间可用10GB期望单批次undo占用不超过500MB那么单批次行数上限 500MB / (500字节 × 3个索引的放大系数约2) ≈ 500MB / 3000字节 ≈ 17万行。但这是理论上限实际还要考虑锁竞争和日志同步开销所以我一般取上限的十分之一左右也就是1.5万行再取个整数1万行。这个计算过程不是绝对的但有计算依据比凭感觉设参数要靠谱得多。迁移前用一小批数据实测一下看看实际undo增长和耗时再调整批次大小是最稳妥的做法。4.4 迁移后的数据校验迁移完成不等于万事大吉数据校验是最后一道关。我一般做三层校验第一层行数校验源表行数减去目标表行数应该等于0全量迁移或等于未迁移的行数部分迁移。SELECT (SELECT COUNT(*) FROM source_table) - (SELECT COUNT(*) FROM target_table) AS diff_count FROM dual;第二层关键字段汇总校验对金额、数量等数值字段做SUM对比对状态字段做GROUP BY对比。-- 金额汇总对比 SELECT SOURCE AS src, SUM(amount) FROM source_table UNION ALL SELECT TARGET, SUM(amount) FROM target_table;第三层抽样明细校验随机抽取几百条记录逐字段对比。这一步能发现汇总校验发现不了的字段映射错误。提示校验SQL最好在迁移前就写好并测试通过迁移完成后直接跑不要临时写。临时写的校验SQL往往有bug反而添乱。4.5 停机窗口的时间估算停机窗口是迁移方案里最敏感的资源。估算方法总迁移时间 数据读取时间 转换时间 写入时间 提交时间单批次时间 × 批次数 总时间再加上索引重建、约束启用、数据校验的时间我一般会按实测单批次时间乘以1.5倍作为估算值留出缓冲。比如实测单批次10秒100个批次就是1000秒乘以1.5是1500秒约25分钟。再加上索引重建和校验停机窗口至少要留40分钟。如果估算下来窗口不够有几个应对手段提前预迁移一部分数据、增加并行度、优化批次大小、把索引重建放到迁移后异步做。5. 常见问题与排查技巧实录5.1 迁移过程中的典型报错与处理迁移过程中遇到的报错八成集中在这几类报错类型典型信息原因处理方式唯一约束冲突ORA-00001目标表已有重复数据迁移前清理或改用MERGE值太大ORA-12899字段长度不够检查源数据实际长度扩目标字段无效数字ORA-01722字符转数字失败检查源数据是否有非数字字符快照过旧ORA-01555undo不足或查询太久增大undo表空间或减小批次死锁ORA-00060并发操作冲突检查是否有其他会话操作目标表空间不足ORA-01653表空间满扩容表空间或清理无用数据ORA-01555是我踩过最深的坑。有一次迁移一张大表批次设得太大跑到一半报快照过旧前面几十分钟白跑。后来把批次从5万降到1万问题就解决了。undo表空间和批次大小是联动的批次越大需要的undo越多。5.2 性能突然变慢的排查思路迁移跑着跑着突然变慢排查顺序一般是看日志表哪个批次开始变慢的是单批次慢还是整体慢。看等待事件查v$session_wait看是在等IO、等锁还是等CPU。看执行计划对慢批次的SQL做EXPLAIN PLAN看是否走了全表扫描。看系统资源CPU、内存、IO、undo使用率哪个到瓶颈了。看是否有其他会话干扰v$lock查是否有锁等待。我遇到过一次迁移到一半突然变慢查下来是目标表的一个触发器在作怪每插入一行就触发一次复杂的业务逻辑。后来迁移期间禁用触发器速度直接翻了五倍。迁移前禁用触发器、约束、索引迁移后再启用是提升性能的常规操作。5.3 数据不一致的定位方法迁移后校验发现数据不一致定位方法先定位到批次用日志表找到行数不一致的批次。再定位到记录在该批次范围内用主键做源表和目标表的FULL OUTER JOIN找出差异记录。最后定位到字段对差异记录逐字段对比找出是哪个字段的问题。-- 找出源表和目标表的差异记录 SELECT s.id, s.col1 AS src_col1, t.col1 AS tgt_col1 FROM source_table s FULL OUTER JOIN target_table t ON s.id t.id WHERE s.id IS NULL OR t.id IS NULL OR s.col1 ! t.col1;5.4 独家避坑经验汇总这些是我在实际项目中总结出来的文档里一般不会写第一迁移前一定要在测试环境完整跑一遍包括失败重试、断点续传、数据校验。测试环境跑通了生产环境才敢动手。第二迁移脚本要进版本库每次修改都有记录。迁移过程中如果需要临时改代码改完要同步回版本库否则下次迁移用的还是旧版本。第三迁移期间关闭不必要的监控告警。迁移会产生大量IO和日志可能触发一堆告警干扰判断。但关键的数据库告警不能关。第四准备一个“一键回滚”脚本。万一迁移失败需要回退不能临时写回滚SQL要提前准备好并测试过。第五迁移完成后不要马上删源数据至少保留一个业务周期。我见过迁移后第二天发现数据有问题但源数据已经删了只能从备份恢复折腾了一整天。第六批次大小不是固定的可以根据数据分布动态调整。数据密集的区间用小批次稀疏的区间用大批次。第七迁移日志表要定期清理否则迁移次数多了日志表本身会变成大表影响查询性能。5.5 迁移方案的扩展与复用这套存储过程方案不只适用于一次性迁移还可以扩展成定期归档任务每月定时把老数据搬到历史库用DBMS_SCHEDULER调度。数据同步任务源表和目标表之间做准实时同步用物化视图或触发器配合。数据清洗任务迁移的同时做数据质量修复比如补全空值、修正异常值。扩展的时候核心框架不用变只需要替换抽取层的查询条件和转换层的规则函数。这也是分层设计的好处改一处不影响全局。我在最近一个项目里把这套方案改成了按月归档每个月1号自动跑把上上个月的数据搬到历史库。跑了半年多没出过问题日志表里清清楚楚记录着每次归档的行数和耗时。这种可重复、可追溯、可回滚的迁移方案才是真正能让人睡个安稳觉的方案。