oracle 大表（1亿以上）迁移笔记一、二

作者：蓝鸟 1974CSDN：https://blog.csdn.net/weixin_42767242关键字大表迁移、存储过程批量归档、定时 JOB、索引维护、统计信息收集、NOLOGGING、BULK COLLECT、FORALL、分区表、分区归档、分区索引一、场景概述（笔记一）在医院 HIS/EMR 系统中，业务流水表、病历明细表数据增长极快，单表数据量轻松突破1 亿条。若不及时归档清理，会引发查询变慢、索引膨胀、数据库 IO 压力高、业务响应卡顿等问题。本文记录 Oracle 超亿级大表在线归档迁移至历史表的落地实操方案：采用存储过程批量分批迁移 + 定时 JOB 调度，同时配套索引管理、统计信息收集、性能优化、日志管控全套落地规范，适合生产直接复用。二、整体设计思路（笔记一）数据留存策略：只保留近 90 天业务热数据，超期数据迁移至历史备份表，原表只留增量常用数据。分批处理机制：采用BULK COLLECT + FORALL批量读取、批量插入、批量删除，避免单行循环性能瓶颈。批次可控：设置单次最大处理条数、每批提交行数，防止一次性加载数据导致 undo、日志暴涨。低峰调度：业务低峰凌晨 1-6 点定时 JOB 执行，避开白天业务高峰期锁竞争与资源争抢。性能加速：迁移期间临时开启NOLOGGING减少重做日志生成，迁移完毕恢复LOGGING保障数据安全。配套运维：迁移完成后维护索引、及时收集表统计信息，保证执行计划不走偏。三、核心存储过程实现（笔记一）3.1 过程设计要点定义全局控制参数：单次最大处理量、每批批量提交大小；通过关联业务主表筛选超期归档数据，住院 + 门诊数据合并筛选；游标批量抓取主键 RID，避免全表扫描多次逻辑读；FORALL 批量插入历史表、批量删除原表数据，分批 COMMIT；异常捕获自动回滚、关闭游标、抛出异常便于 JOB 监控告警；迁移前后自动切换表日志模式，兼顾速度与数据安全。3.2 完整存储过程代码sqlCREATE OR REPLACE PROCEDURE PRO_EMR_BL_DLNR_TOLS ASV_DATE DATE;-- 全局控制参数：单次任务最大处理条数V_ONCE_MAX PLS_INTEGER := 1000000;-- 每批批量处理提交条数V_BATCH_SIZE CONSTANT PLS_INTEGER := 1000;BEGIN-- 设定保留近90天热数据V_DATE := TRUNC(SYSDATE) - 90;-- 迁移期间临时关闭日志，提升插入删除性能BEGIN EXECUTE IMMEDIATE 'ALTER TABLE emr_bl_dlnr NOLOGGING'; EXCEPTION WHEN OTHERS THEN NULL; END;BEGIN EXECUTE IMMEDIATE 'ALTER TABLE emr_bl_dlnr_ls NOLOGGING'; EXCEPTION WHEN OTHERS THEN NULL; END;DECLARE-- 游标筛选需归档的主键数据：住院+门诊超期数据CURSOR C_DATA ISSELECT rid AS RIDFROM (-- 住院超期数据筛选SELECT r.jlxh AS RIDFROM emr_bl_dlnr rWHERE r.zymz = 1AND EXISTS (select 1from zy_brry ywhere y.zyh = r.jzhmand y.cypb = 8and y.cyrq v_date )UNION ALL-- 门诊超期数据筛选SELECT r.jlxh AS RIDFROM emr_bl_dlnr rWHERE r.zymz = 2AND EXISTS (select 1from ys_mz_jzls lwhere l.jzxh = to_number(r.jzhm)and l.kssj v_date ))WHERE ROWNUM = V_ONCE_MAX;TYPE T_RIDS IS TABLE OF C_DATA%ROWTYPE INDEX BY BINARY_INTEGER;V_RIDS T_RIDS;BEGINOPEN C_DATA;