安全数据清理：从大型 Oracle 表删除旧数据
安全数据清理：如何从大型 Oracle 表中删除旧数据
为了遵守数据保留规定，我们定期清理旧数据。但是，从生产数据库删除数百万条记录需要非常谨慎。以下是我们的做法。
第一步：数据目录与计划
- 统计所有表并确定保留期限
- 与法律团队确认保留计划
- 与其他团队协作，识别下游依赖
第二步：备份表
删除前备份受影响表，确保出现问题时可以快速恢复。
第三步：理解表结构
先识别子表和父表。先删除子表，以避免外键约束错误。
第四步：批量删除 vs 直接删除
在 PT 环境中测试批量删除正常。但在生产环境中：
- 表超过 700 万行，需要删除超过 100 万行
- 20K 批量删除导致等待 1.5 小时并触发 FK 错误
- 出现自动回滚
现在采用增量删除：
- 每次删除 1 万条并提交
- 循环执行直到清理完成
- 速度慢但可控，易于定位 FK 错误
第五步：SQL 示例
-- 删除旧数据示例
SELECT '子表1', COUNT(*) FROM child_table_1 WHERE created_on < ADD_MONTHS(TRUNC(SYSDATE), -144);
DELETE FROM child_table_1 WHERE created_on < ADD_MONTHS(TRUNC(SYSDATE), -144);
COMMIT;
DECLARE
v_batch_size CONSTANT PLS_INTEGER := 20000;
v_rows PLS_INTEGER;
v_cutoff_date DATE := ADD_MONTHS(TRUNC(SYSDATE), -144);
BEGIN
LOOP
DELETE FROM large_table
WHERE created_on < v_cutoff_date
AND ROWNUM <= v_batch_size;
v_rows := SQL%ROWCOUNT;
COMMIT;
EXIT WHEN v_rows < v_batch_size;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
关键要点
- 大表使用增量批量删除
- 频繁提交，避免大规模回滚
- 先删除子表，确保外键约束
- 删除前后检查记录数
- 删除后收集统计信息，维持查询性能
- 考虑程序化清理：由于交易量快速增长，手动批量删除可能在 6–12 个月后再次变慢。自动化清理可以持续维护表性能。
❤️ Support This Blog
If this post helped you, you can support my writing with a small donation. Thank you for reading.
Comments
Post a Comment