ogg中的MV问题

最新推荐文章于 2022-06-02 19:53:43 发布

原创最新推荐文章于 2022-06-02 19:53:43 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#ogg #MV

GoldenGate 专栏收录该内容

11 篇文章

订阅专栏

在使用 Oracle GoldenGate (OGG) 搭建生产环境时，遇到MV（Materialized Views）的问题。不支持带有ROWID的MV，且必须有主键。Truncates不被支持，需要使用DELETE FROM语句。解决方法包括将MV改为'FOR UPDATE'或在目标端重建MV和MV日志。测试表明，'FOR UPDATE'取决于业务需求，而带有ROWID的MV在OGG中也可以工作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近在搭建生产环境的ogg时，遇到了MVIEW的问题
If your source system has materialized views, please pay attention to the following:

Limitations of support for materialized views (MVs)
● Materialized views created WITH ROWID are not supported.
● The materialized view log can be created WITH ROWID.
● The source table must have a primary key.
● Truncates of materialized views are not supported. You can use a DELETE FROM statement.
● Some Oracle GoldenGate initial-load methods do not support LOBs in a materialized view.
● For Replicat, the materialized view must be updateable.
● Full refreshes are supported for Oracle 10g and later.

A solution is to change the materialized views “FOR UPDATE” before replicating this table.

If you don’t do this REPLICAT will abend with this message:

2011-10-15 21:14:44 WARNING OGG-00869 OCI Error ORA-01732: data manipulation operation not legal on this view (status = 1732), SQL <INSERT INTO "SCHEMA_TAR"."MV_TABEL" ("ID","STR","DATUM") VALUES (:a0,:a1,:a2)>.
You can also enable DDL support and exclude the SNAPSHOTS (if they are in your mapping):

DDL INCLUDE all, EXCLUDE OBJTYPE 'SNAPSHOT'

http://mverzijl.wordpress.com/2011/10/15/materialized-views-with-goldengate/

我测试了rowid的MV，replicat会abend，我就把MV和MV_log都mapexclude了，然后在target端重新生成日志和MV（基础表是数据一致的），未见异常。
经过测试发现，加上for update也是可以的，但是要看业务需要了。

如果有要这样做的，还希望做下实验哦
SQL> select * from ttt;
T1 T2
---------- ----------
10 2
4 2
SQL> alter table ttt modify (t1 number primary key);
SQL> create materialized view log on ttt with primary key;
SQL> create materialized view mv_ttt
build immediate
refresh fast on demand
with primary key
start with to_date('2014-12-24 23:28:50','YYYY-MM-DD HH24:MI:SS') NEXT SYSDATE + 10/1440
as
select * from ttt;
可以，没有问题

尝试rowid的for update
SQL> create table forr as select * from ttt;
SQL> create materialized view log on forr with rowid;
SQL> create materialized view mv_forr
build immediate
refresh fast on demand
with rowid
start with to_date('2014-12-24 23:28:50','YYYY-MM-DD HH24:MI:SS') NEXT SYSDATE + 10/1440
for update
as
select * from forr;
可以，没有问题