ORA-23413: table "DCS_BIZ"."TMP_12" does not have a materialized view log

最新推荐文章于 2022-03-20 01:08:16 发布

Snowballl

最新推荐文章于 2022-03-20 01:08:16 发布

阅读量6.3k

点赞数

分类专栏： oracle解决方案实现

oracle解决方案实现专栏收录该内容

43 篇文章 0 订阅

订阅专栏

一、报错

SQL>
SQL> CREATE MATERIALIZED VIEW MV_TMP_12
2        COMPRESS
3        NOLOGGING
4        BUILD DEFERRED
5        REFRESH fast
6        START WITH sysdate NEXT sysdate+ 1/1440
7        AS
8        select count(*),name from tmp_12
9        group by name;

CREATE MATERIALIZED VIEW MV_TMP_12
      COMPRESS
      NOLOGGING
      BUILD DEFERRED
      REFRESH fast
      START WITH sysdate NEXT sysdate+ 1/1440
      AS
      select count(*),name from tmp_12
      group by name

ORA-23413: table "DCS_BIZ"."TMP_12" does not have a materialized view log

二、原因分析

使用fast模式刷新物化视图，需要有物化视图日志

三、解决方案

CREATE MATERIALIZED VIEW LOG ON TMP_12;

SQL> CREATE MATERIALIZED VIEW MV_TMP_12
2    refresh FAST on DEMAND
3   START WITH sysdate NEXT sysdate+ 1/1440
4   enable query rewrite
5   AS
6   select * from tmp_12;

Materialized view created

注：说明fast模式刷新物化视图，是增量刷新方式，需要有物化视图日志