上周线上有个报表库物化视图刷新本来只要3分钟突然变成40分钟。查了一圈发现负责维护的同事图省事把基表上的物化视图日志MATERIALIZED VIEW LOG给drop了刷新直接退化成了全量COMPLETE。这个案例我后面细说。今天想认真聊聊Oracle物化视图日志——它是什么、如何用CREATE MATERIALIZED VIEW LOG创建、为什么它能记录基表的DML变更以及刷完日志去哪了。写这篇的起因是群里常有人问“为什么我的快速刷新不生效”“日志怎么一直涨”“ORA-23413怎么破”所以我按自己的排查经验把这一套逻辑完整梳理一遍给做数据同步、报表中间层、数据仓库的同学一个可以直接抄作业的参考。1. 物化视图日志解决了什么问题1.1 从物化视图刷新机制看日志存在的意义先抛个结论物化视图日志Materialized View Log也叫snapshot log是Oracle专门为物化视图快速刷新FAST REFRESH准备的一张“变更流水账”。它记录基表上发生的INSERT、UPDATE、DELETE等DML操作物化视图做增量刷新时靠这张账本就知道“哪些行的数据变了、怎么变的”不需要回源表扫全表比较。物化视图刷新方式有三种COMPLETE全量重建、FAST增量刷新、FORCE优先FAST不行就COMPLETE。其中FAST刷新是生产环境最想要的因为它只处理变更数据耗时短、对源库压力小。但FAST刷新有个硬前置条件基表上必须存在对应的物化视图日志。如果你试图对一个没有日志的表做快速刷新Oracle会直接抛ORA-23413materialized view does not have a materialized view log。这时候DBA如果没仔细看报错直接把刷新改成FORCE甚至COMPLETE短时间能跑通但数据量上来后同步窗口越拉越长最终酿成我开头说的那种线上事故。用生活里的例子打比方物化视图相当于一张汇总报表你没日志的时候每次都要把原始单据重新翻一遍才能出报表这叫全量刷新有日志以后每次只把“新增加的单据”“改过的单据”挑出来更新到报表上这叫增量刷新。日志就是那本记录单据变更的流水账本。正是因为日志决定了物化视图能不能走增量路径选择正确的日志策略、维护好日志状态就成了数据同步链路里绕不开的环节。本文所有演示基于Oracle 19c但核心语法和原理在11g到21c都通用。1.2 DML和DDL的区别以及日志只关心DML这件事群里经常有人把DDL和DML混着说这两个概念虽然只差一个字母但含义完全不同对物化视图日志来说更是“一个管、一个不管”的边界。DMLData Manipulation Language是数据操作语言包括INSERT、UPDATE、DELETE、MERGE它改变的是表里的数据内容。物化视图日志记录的就是这一类操作。每次对基表执行DML相关行的主键值或ROWID、操作类型I/U/D、变更向量等信息都会写入日志供后续刷新使用。DDLData Definition Language是数据定义语言包括CREATE、ALTER、DROP、TRUNCATE它改变的是表结构而不是数据内容。物化视图日志不记录DDL操作。这里有个特别阴间的坑TRUNCATE在Oracle里属于DDL虽然它把表数据清空了但不会往物化视图日志里写任何记录。我见过不止一次开发同学对基表执行了TRUNCATE然后发现物化视图快速刷新出来的数据还是旧的或者刷新直接报状态异常就是因为TRUNCATE没有通过日志“留痕”。对比项DMLDDL典型语句INSERT、UPDATE、DELETE、MERGECREATE、ALTER、DROP、TRUNCATE改变内容表数据内容表结构/对象定义是否记录到物化视图日志是否对物化视图的影响可通过快速刷新增量同步可能导致物化视图失效或需要完整刷新这个区别在实际故障排查里特别有用。一旦发现物化视图数据和基表对不上第一反应不要只盯着日志记录先查一下有没有人对基表执行过DDL尤其是TRUNCATE。如果确实执行过日志和物化视图的一致性已经被破坏别想着靠续传修补直接重建物化视图或做一次完整刷新才是正路。2. CREATE MATERIALIZED VIEW LOG 语法与参数逐项拆解2.1 从最简到最全的创建语句先看最基础的创建语句在基表上建日志只需要一行SQLCREATE MATERIALIZED VIEW LOG ON sales;这个写法有什么特点Oracle默认会采用WITH PRIMARY KEY方式记录日志也就是说基表必须有主键日志表里会记录主键列的值。如果基表没有主键这条语句会直接报ORA-12052之类的错误此时必须改为WITH ROWID方式CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;但生产环境通常不会用这么简单的写法因为复杂的快速刷新场景对日志有额外要求。一个真正能应对大多数业务场景的完整写法如下CREATE MATERIALIZED VIEW LOG ON sales WITH PRIMARY KEY, ROWID SEQUENCE INCLUDING NEW VALUES PURGE AFTER 30 DAYS;拆开解释一下WITH PRIMARY KEY记录主键列适合有主键且物化视图通过主键关联的场景。WITH ROWID记录行的物理地址适合没有主键或者物化视图需要精确到物理行的场景。SEQUENCE增加一个序列号用来区分同一行在短时间内发生的多次DML操作。没有它快速刷新在某些场景下会分不清先后顺序。INCLUDING NEW VALUES把UPDATE之后的新值也写进日志聚合类物化视图做快速刷新时基本必备。PURGE AFTER 30 DAYS日志条目最多保留30天超期自动清理防止日志无限膨胀。这些参数不是随便乱加的后面2.2和2.3会单独讲选型逻辑。建日志时还会自动创建一系列内部对象最核心的是MLOG$_基表名这个日志表这个在第三章展开。2.2 参数选择的判断标准主键还是ROWID主键和ROWID两种方式各有适用场景选错了后面维护成本会明显上升。先说结论基表有稳定主键且物化视图通过主键关联的优先用WITH PRIMARY KEY基表没有主键或者物化视图在SELECT里明确需要访问ROWID的用WITH ROWID。为什么优先主键主键语义稳定。业务表的主键在数据刷新过程中不会频繁变化即使行的物理位置变了比如表重建、分区移动主键还是那个主键物化视图可以通过主键关联正确找到对应的刷新目标。ROWID是物理地址一旦行迁移、分区合并ROWID就变了物化视图里的历史ROWID就失效了。但有些表天生没有主键比如某些日志流水表、外部接入数据表这时候只能退而求其次用WITH ROWID。需要特别留意的是当物化视图本身需要快速刷新且基于连接查询时SELECT列表里必须带上各基表的ROWID否则Oracle拒绝走FAST路径。实际开发里还有一种常见组合WITH PRIMARY KEY, ROWID同时带上。这不是画蛇添足而是为了让物化视图在不同刷新策略之间灵活切换。只带主键时如果物化视图的查询里不小心漏了主键列快速刷新可能会报错同时带上ROWID能给优化器更多选择。代价是日志表会多存一列物理地址占用一点空间但通常是值得的。在性能敏感的大表上建日志前评估一下DML开销非常必要。日志的写入在DML发生时同步进行等于每次INSERT/UPDATE/DELETE都增加了一次额外的写入成本。实测下来字段越多、包含新值日志开销越大尤其是UPDATE频繁的表日志写入成本可能让整体DML性能下降10%到20%。如果业务不能接受可以退而求其次只记录必要字段或者调整刷新频率减少日志积压。2.3 日志的清理策略PURGE参数物化视图日志如果不加清理策略会像流水账一样一直积累。正常情况下物化视图刷新完成后会消费掉这些日志但刷新频率低、刷新失败、存在多个物化视图共用同一日志的时候日志会积压几十万上百万行都是可能的。日志表太大不仅占用空间还会让后续每次刷新扫描日志变慢形成恶性循环。Oracle提供PURGE子句来控制日志保留期限。最简单的写法是按天数清理CREATE MATERIALIZED VIEW LOG ON sales WITH PRIMARY KEY PURGE AFTER 15 DAYS;也可以写成基于刷新次数的形式CREATE MATERIALIZED VIEW LOG ON sales WITH PRIMARY KEY PURGE AFTER 5 REFRESHES;更灵活的是定时清理比如每天凌晨清理一次CREATE MATERIALIZED VIEW LOG ON sales WITH PRIMARY KEY START WITH SYSDATE NEXT SYSDATE 1;这里有一个很重要的实操细节PURGE策略只是在日志条目“不再需要”的时候才允许清理如果某个物化视图还没有完成刷新日志就算到了保留期限也不能被清Oracle会先保证刷新一致性。所以不要以为加了PURGE就可以高枕无忧刷新失败时日志照样会涨。日常维护中我习惯用下面这条SQL查看日志大概占了多少空间SELECT segment_name, bytes / 1024 / 1024 AS size_mb FROM user_segments WHERE segment_name IN (MLOG$_SALES, TMP$_SALES, RUPD$_SALES);如果发现MLOG$_表已经涨到几GB先查物化视图刷新状态确认没有刷新锁再手动清理BEGIN DBMS_MVIEW.PURGE_LOG( master SALES, num 100000, flag DELETE ); END;num参数表示每次删除的批大小太大容易产生大量归档日志太小又删得慢实践中10000到100000之间比较合适。手动清理治标不治本核心还是保证物化视图刷新按时成功让日志正常消费。3. 物化视图日志的内部结构与实践验证3.1 MLOG$_表的真实结构光说不练假把式。我建一张测试表然后建日志带大家看看日志表里到底存了什么。先执行CREATE TABLE sales ( id NUMBER PRIMARY KEY, product_id NUMBER, quantity NUMBER, amount NUMBER ); CREATE MATERIALIZED VIEW LOG ON sales WITH PRIMARY KEY, ROWID SEQUENCE INCLUDING NEW VALUES;此时Oracle自动创建了MLOG$_SALES、TMP$_SALES等对象。查看日志表结构DESC MLOG$_SALES;你会看到类似这样的列SNAPTIME$$、DMLTYPE$$、OLD_NEW$$、CHANGE_VECTOR$$、ID、ROWID、SEQUENCE$$。简单解释几个关键列SNAPTIME$$记录这条日志被哪个刷新批次处理刷新完成后Oracle用系统时间戳标记表示这条日志已消费。DMLTYPE$$操作类型I代表INSERTU代表UPDATED代表DELETE。OLD_NEW$$标示新旧值O代表旧值N代表新值。CHANGE_VECTOR$$变更向量记录UPDATE具体改了哪些列用于精准刷新物化视图。SEQUENCE$$WITH SEQUENCE时才会真正写入用来保证同一行的多次变更顺序正确。ID、ROWID主键值或物理地址是物化视图定位目标行的关键。除了MLOG$_表系统中还会出现TMP$_SALES、RUPD$_SALES之类的辅助表。TMP$_是刷新过程中的临时中转表RUPD$_在新值日志场景下记录UPDATE的新旧值。这些表不用手动维护但如果看到它们占用空间异常多半是刷新卡死或有人手工动过日志需要留意。对照一下最容易混淆的概念物化视图日志不是物化视图的数据副本它只记录“变更信息”不存全量数据。这也是它轻量、适合频繁刷新的原因。3.2 一次DML操作在日志里到底发生了什么我们可以通过一个完整操作链看日志怎么记录、怎么被消费。接着上面的表和日志对sales执行几条DMLINSERT INTO sales VALUES (1, 100, 2, 200); INSERT INTO sales VALUES (2, 100, 1, 100); UPDATE sales SET quantity 5 WHERE id 1; DELETE FROM sales WHERE id 2; COMMIT;查看日志表内容SELECT DMLTYPE$$, OLD_NEW$$, ID, ROWID, SEQUENCE$$ FROM MLOG$_SALES ORDER BY SEQUENCE$$;结果会出现多行记录两条INSERT的I/N记录、一条UPDATE的U/N记录因为INCLUDING NEW VALUES记录了新值、一条DELETE的D/O记录。每条日志都包含了主键ID和ROWID这样物化视图刷新时就能拿着这些信息去同步对应行。我在测试环境里模拟过如果去掉INCLUDING NEW VALUESUPDATE操作在日志里通常只有U/O旧值记录基于聚合的物化视图刷新逻辑会非常受限这也是很多聚合物化视图快速刷新建不出来的原因。接下来创建物化视图并做快速刷新CREATE MATERIALIZED VIEW mv_sales_summary REFRESH FAST ON DEMAND AS SELECT product_id, SUM(quantity) AS total_qty, SUM(amount) AS total_amount FROM sales GROUP BY product_id; BEGIN DBMS_MVIEW.REFRESH(MV_SALES_SUMMARY, F); END; /刷新完成后再次查询MLOG$_SALES会发现日志已经被清空或标记。刷新动作本质上就是“读取日志、把变更合并到物化视图、把处理过的日志清理掉”三步。如果对基表继续做一批新DML日志会重新累积下一个刷新批次再继续消费周而复始。实操中建议用以下语句验证物化视图到底能不能FAST刷新BEGIN DBMS_MVIEW.EXPLAIN_MVIEW(MV_SALES_SUMMARY); END; /EXPLAIN_MVIEW结果里专门有一列CAPABILITY_STATUS如果显示REFRESH_FAST_AFTER_INSERT为POSSIBLE说明快速刷新路径是通的如果显示IMPOSSIBLE后面会跟原因比IRREFRESHABLE之类的提示友好得多。这一步在建好物化视图后马上做别等到上线了才发现刷新路径有问题。4. 常见错误与排查技巧实录4.1 ORA-23413、ORA-12034、ORA-12031等经典报错物化视图日志相关的报错很多都是“日志缺失”“刷新滞后”“日志被清”这三类问题我整理了一张速查表都是实践里真实见过的场景报错信息常见原因处理方式ORA-23413: materialized view does not have a materialized view log基表上没建日志或日志被drop重新创建物化视图日志ORA-12034: materialized view log younger than last refresh日志记录时间比物化视图最近刷新时间还新刷新历史不一致重新完整刷新或重建物化视图ORA-12031: materialized view log on table conflicts with index日志相关对象与索引冲突检查同名索引/对象重建日志对象ORA-12052: cannot fast refresh materialized view物化视图SQL结构不满足快速刷新条件检查物化视图定义补全ROWID/聚合条件ORA-3232: cannot use materialized view log because it references X日志里缺少物化视图所需列重建日志并包含必要列ORA-12034是高频问题尤其常见于有人对基表执行了手动清理日志操作或者物化视图刷新历史信息被重置。遇到它别硬撑直接执行一次完整刷新让一致状态恢复BEGIN DBMS_MVIEW.REFRESH(MV_SALES_SUMMARY, C); END; /如果物化视图很多可以考虑重建相关日志并刷新所有归属它的物化视图保证日志版本和物化视图版本对齐。一般情况下不到万不得已不建议直接动日志动错一步比报错更麻烦。4.2 日志膨胀和手动清理实战日志膨胀是DBA咨询量很高的问题。表现是MLOG$_表占了几GB查询和刷新都变慢。膨胀的原因往往不是单一因素常见的组合是物化视图刷新频率低、业务DML量大、刷新失败没有预警。处理顺序很重要。先查刷新是否正常SELECT mowner, master, last_refresh_date, status FROM user_mview_analysis WHERE master SALES;再看刷新锁SELECT * FROM v$lock WHERE type JI AND id1 (SELECT obj# FROM obj$ WHERE name MLOG$_SALES);确认没有刷新锁或刷新进程卡死后再执行手动 purge避免出现“删了日志导致进行中的刷新失败”的连锁问题。我见过有的人一上来就DELETE MLOG$_不仅没解决膨胀反而把正在刷新的物化视图搞坏了。手动清理还有一种常用方法是直接用PURGE_LOG包前面已经写过。值得一提的是物化视图日志在刷新完成后会自动删除已处理的条目正常情况下根本不需要手动清理。如果你发现自己天天在手动清日志说明刷新链路本身一定有异常重点排查刷新计划是否正常执行、物化视图是否处于失效状态。4.3 分区表、连接物化视图对日志的额外要求很多生产表的体量已经走上分区路线。基表是分区表时物化视图日志最好也配合分区否则日志表会成为新的瓶颈。Oracle支持在分区表上创建分区日志例如CREATE MATERIALIZED VIEW LOG ON sales WITH PRIMARY KEY PURGE AFTER 30 DAYS ON PREBUILT TABLE;更稳妥的方式是在建日志时指定分区策略让日志表和基表按照相同分区键对齐。分区日志的优势是清理历史日志可以走分区裁剪删除过期日志效率高得多不会产生海量单条DELETE。如果基表做了分区维护比如SPLIT、MERGE分区日志也需要同步维护这些操作建议放在同一个变更窗口内评估。再说连接物化视图多表JOIN的情况。快速刷新要求每一个参与连接的基表都有物化视图日志不是只有主表有日志就行。假设物化视图是sales JOIN products那么sales和products两张表都需要日志而且物化视图SELECT里通常需要包含各表的ROWID或主键。一个常见坑是建products日志时只用了WITH PRIMARY KEY但物化视图里的连接条件引用的是products的ROWID结果刷新报错又回去改日志。聚合物化视图对日志要求更严格。聚合函数、GROUP BY列都必须能被日志和刷新机制支持COUNT、SUM这类常用聚合还好AVG在快速刷新下的处理逻辑比较绕因为需要同时维护COUNT和SUM才能算回平均值。复杂的分析函数很多根本不支持快速刷新。所以在设计阶段就应当用EXPLAIN_MVIEW验证刷新能力而不是上线后再补救。5. 个人实操经验补充最后分享几条这些年踩坑踩出来的经验。第一建日志前先列一个检查清单基表有没有主键有就带PRIMARY KEY物化视图是否需要访问ROWID需要就带ROWID物化视图是不是聚合类是就必须带SEQUENCE和INCLUDING NEW VALUES日志清理策略有没有设置没设置就补PURGE AFTER N DAYS。四条全过再执行CREATE语句。第二生产环境建日志前做DML性能基线对比。选一个业务低峰期先跑一批代表业务特征的DML统计耗时建完日志再跑同一批对比增量。日志列越少、不记录新值开销越小。如果UPDATE量极大可以考虑只记录主键加SEQUENCE牺牲部分刷新效率换取DML性能。第三监控要跟上。我习惯每天检查一次大日志表的空间占用查询语句简单直接SELECT segment_name, ROUND(bytes / 1024 / 1024, 2) size_mb FROM user_segments WHERE segment_name LIKE MLOG$% ORDER BY size_mb DESC;配合物化视图刷新历史基本能做到日志问题早发现早处理不至于等到报表超时再救火。第四开发环境尽量模拟生产刷新策略。很多开发库建物化视图时图省事直接REFRESH FORCE根本没有日志到了生产环境复制过去就踩ORA-23413。开发阶段就把日志建好用FAST刷新跑通上线时能少很多折腾。物化视图日志是Oracle增量同步体系里非常关键的一块拼图创建语句不复杂但参数选型、生命周期管理、异常诊断环环相扣。希望这份实操经验能帮大家少走一些弯路遇到快速刷新相关问题时至少有一个清晰的排查方向。