Oracle中如何使用SQL创建物化视图日志-SQL-PHP中文网

物化视图日志必须基于主键或rowid创建，否则报ora-12013；推荐优先用with primary key，若无主键则用with rowid并配合refresh fast on commit显式声明；需支持update时须同时指定with primary key、sequence和including new values。

oracle中如何使用sql创建物化视图日志

物化视图日志必须基于主键或ROWID创建

Oracle要求物化视图日志（MATERIALIZED VIEW LOG）的基表必须有主键，或者显式指定WITH ROWID。没有主键又没加ROWID会直接报错：ORA-12013: updatable materialized view must have a primary key（即使只是建日志，不是建可更新物化视图，这个限制也生效）。

实操建议：

优先用CREATE MATERIALIZED VIEW LOG ON table_name WITH PRIMARY KEY; —— 这是最稳妥、兼容性最好的方式
若表确实无主键且无法加，可用WITH ROWID，但后续基于该日志的物化视图必须显式声明REFRESH FAST ON COMMIT时带上WITH ROWID，否则刷新失败
避免混用PRIMARY KEY和ROWID；一旦选了ROWID，就不能在日志里再加SEQUENCE或INCLUDING NEW VALUES（Oracle会报ORA-32401）

WITH SEQUENCE和INCLUDING NEW VALUES影响增量刷新能力

WITH SEQUENCE用于支持FAST刷新中的UPDATE操作（不只是INSERT/DELETE），而INCLUDING NEW VALUES则记录更新前后的完整行值——这两者共同决定了物化视图能否做真正的“快速更新”。缺一不可时，REFRESH FAST可能退化为COMPLETE，且DBA_MVIEW_LOGS中LOGGING列仍为YES，但DBA_MVIEWS里STALENESS可能变成UNUSABLE。

实操建议：

需要支持UPDATE的场景，必须同时写：WITH PRIMARY KEY, SEQUENCE, INCLUDING NEW VALUES
INCLUDING NEW VALUES会显著增大日志表体积（多存一份新旧行），生产环境要评估空间开销
如果只做INSERT/DELETE同步（比如ETL加载流水表），可省略SEQUENCE和INCLUDING NEW VALUES，节省存储

物化视图日志名不能自定义，但可查其底层表

Oracle自动为物化视图日志生成系统命名（如MLOG$_EMP），你无法在CREATE语句中指定名字。但它本质是一张普通堆表，存于当前用户schema下，可通过SELECT * FROM USER_TABLES WHERE TABLE_NAME LIKE 'MLOG$%';查到。

oracle知识库

oracle知识库下载

下载

实操建议：

不要试图DROP TABLE MLOG$_xxx——应使用DROP MATERIALIZED VIEW LOG ON table_name;，否则元数据不一致，后续建物化视图会报ORA-12002
日志表默认无索引，高并发DML下可能成瓶颈；Oracle 12c+会自动在SNAPTIME$$列上建索引，旧版本建议手工加：CREATE INDEX idx_mlog_emp_snap ON mlog$_emp(snaptime$$);
日志表不继承原表的分区属性，即使基表是分区表，日志表仍是普通表

建日志前必须确保用户有CREATE MATERIALIZED VIEW权限

很多人卡在ORA-01031: insufficient privileges，其实不是缺CREATE TABLE，而是缺CREATE MATERIALIZED VIEW权限——因为物化视图日志本质上是为物化视图服务的底层结构，Oracle强制绑定该权限。

实操建议：

授权命令就是：GRANT CREATE MATERIALIZED VIEW TO username;（注意不是CREATE MATERIALIZED VIEW LOG，后者不存在）
如果用DBA账号建好日志，再授权给其他用户查询基表，那些用户依然不能基于该日志建自己的物化视图——建物化视图的动作本身也需要CREATE MATERIALIZED VIEW权限
在PDB环境中，还需确认当前容器是否已启用物化视图功能（ALTER PLUGGABLE DATABASE ENABLE GPD在某些版本是前提）

实际建日志时最常被忽略的是：基表主键变更后，已有日志不会自动更新字段列表，必须先DROP再重建；而线上表做主键调整本就敏感，这事容易拖到物化视图刷新异常才暴露。