Oracle 11g 复制 - 对远程数据库(数据库链接)使用提交时刷新 [英] Oracle 11g Replication - Using refresh on commit with remote database (database links)

问题描述

下午好，

我有 3 个数据库；SID 是 config、prod1 和 prod2.

I have 3 databases; the SIDs are config, prod1 and prod2.

我使用物化视图将配置数据库中 11 个表上的数据复制到其他两个数据库.物化视图目前每五秒刷新一次，但如果它们在提交时更新将是理想的.

I am using Materialized Views to replicate data on 11 tables in the config database onto the other two databases. The Materialized Views are currently refreshing every five seconds but it would be ideal if they were updated on commit.

我遇到了这个网站，它解释了从远程数据库复制时不支持提交.

I came across this website that explains that when replicating from a remote database that on commit is not supported.

这是我期待的工作

CREATE MATERIALIZED VIEW "schema"."table" USING INDEX REFRESH FORCE ON COMMIT AS select column1 from schema.table@config;

链接中建议使用按需使用主键快速刷新"方法，但显然这是按需使用的.我想知道如果可能的话，为了让提交时刷新的环境运行，任何人可能有什么想法?

The method "refresh fast on demand with primary key" is suggested in the link but obviously this is on demand. I am wondering what ideas anyone may have in order to get a refresh-on-commit environment running if possible?

谢谢

推荐答案

您无法创建在从远程表提交时刷新的物化视图.来自文档:

You can't create a materialized view refreshed on commit from a remote table. From the documentation:

刷新 ON COMMIT 的限制

包含对象类型或 Oracle 提供的类型的物化视图不支持此子句.

This clause is not supported for materialized views containing object types or Oracle-supplied types.

具有远程表的物化视图不支持此子句.

This clause is not supported for materialized views with remote tables.

原因是数据库链接是在子"数据库中定义的，而不是在父"数据库中.因此，父数据库不可能自己触发或修改子数据库中的任何内容.

The reason is that the database link is defined in the "child" database, not in the "parent" database. Therefore, the parent database can't possibly trigger or modify anything in the child database on its own.

如果你想要一个表的 100% 实时副本，我建议使用视图.

If you want a 100% real-time copy of a table, I suggest a view.

如果您想在提交时复制数据，您可以修改您的 DML 过程，以便它们同时更新子远程表.

If you want to replicate the data on commit, you could modify your DML procedures so that they update the children remote tables at the same time.

这篇关于Oracle 11g 复制 - 对远程数据库(数据库链接)使用提交时刷新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！