在 Oracle 中保持表同步
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/965321/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Keeping tables synchronized in Oracle
提问by Andy
We're about to run side-by-side testing to compare a legacy system with a new shiny version. We have an Oracle database table, A, that stores data for the legacy system, and an equivalent table, B, that stores data for the new system, so for the duration of the test, the database is denormalized. (Also, the legacy system and table A are fixed - no changes allowed)
我们即将进行并行测试，以将旧系统与新的闪亮版本进行比较。我们有一个 Oracle 数据库表 A，用于存储遗留系统的数据，以及一个等效的表 B，用于存储新系统的数据，因此在测试期间，数据库是非规范化的。（此外，遗留系统和表 A 是固定的 - 不允许更改）
What I want to do is to allow the infrequent DML operations on A to propagate to B, and vice-versa. I started with a pair of triggers to do this, but hit the obvious problem that when the triggers run, the tables are mutating, and an exception is thrown.
我想要做的是允许 A 上不频繁的 DML 操作传播到 B，反之亦然。我开始使用一对触发器来执行此操作，但遇到了一个明显的问题，即当触发器运行时，表会发生变化，并且会引发异常。
Is there a standard way of handling this issue? I've read differing reports on whether or not using dbms_scheduler is the way to go...
有处理这个问题的标准方法吗？我已经阅读了关于使用 dbms_scheduler 是否可行的不同报告......
Thanks,
谢谢，
Andy
安迪
Update:I've ended up chickening out of the whole issue and ensured that all stored procedures that update A, also update B, and vice versa.
更新：我最终解决了整个问题，并确保所有更新 A 的存储过程也更新 B，反之亦然。
I've marked Quassnoi's answer as accepted, because I'd follow his suggestions if faced with the same issue in the future.
我已将 Quassnoi 的回答标记为已接受，因为如果将来遇到同样的问题，我会遵循他的建议。
I've marked up JosephStyon's answer, because I briefly got things working by adding two insert/update statement level triggers on tables A and B, then doing his merge procedure using A or B as the master table, depending on which trigger ran (although first I checked that the target table would be changed by the merge, earlying out if not).
我已经标记了 JosephStyon 的答案，因为我通过在表 A 和 B 上添加两个插入/更新语句级触发器，然后使用 A 或 B 作为主表执行他的合并过程，这取决于哪个触发器运行（尽管首先，我检查了目标表是否会因合并而改变，否则就提前退出）。
采纳答案by Quassnoi
I'd create Aand Bas views over a single normalized (or denormalized) table, and created an INSTEAD OFtrigger over these views to handle DMLoperations.
我会在单个规范化（或非规范化）表上创建A和B作为视图，并INSTEAD OF在这些视图上创建触发器来处理DML操作。
If the query plans matter, it's better to keep two copies of tables: A_underlyingand B_underlyingand create the views just like this:
如果查询计划很重要，最好保留两个表副本：A_underlying并B_underlying像这样创建视图：
CREATE VIEW A
AS
SELECT *
FROM A_underlying
CREATE VIEW B
AS
SELECT *
FROM B_underlying
The predicates will be pushed into the views, and the query plans for actual tables and views will be the same.
谓词将被推送到视图中，并且实际表和视图的查询计划将是相同的。
In INSTEAD OFtriggers over both view, you should put the data into both underlying tables.
在INSTEAD OF两个视图的触发器中，您应该将数据放入两个基础表中。
回答by Kosi2801
Do you really mean DDL, not DML?
你真的是说 DDL，而不是 DML？
With DML you can have a look at Oracles Multi Master Replicationto keep the tables in synch or you could also have a look at the tool SymmetricDSfor this purpose.
使用 DML，您可以查看 Oracles Multi Master Replication以保持表同步，或者您也可以查看用于此目的的工具SymmetricDS。
With DDL the only solution I'm aware of is again the Oracle advanced replication.
对于 DDL，我知道的唯一解决方案是Oracle 高级复制。
回答by JosephStyons
Put the below three statements in a stored procedure, then run it as a scheduled job as often as you like:
将以下三个语句放在一个存储过程中，然后根据需要将其作为计划作业运行：
--Assume that "A" is a master, and "B" needs to be synched
--If no match in "A", delete from "B"
DELETE FROM B
WHERE NOT EXISTS(
SELECT *
FROM A
WHERE A.PRIMARY_KEY = B.PRIMARY_KEY
);
--If there is a match, but they are different, then update "B"
update
(
select
a.field1 as new_value1
,b.field1 as old_value1
,a.field2 as new_value2
,b.field2 as old_value2
,....
,a.fieldN as new_valueN
,b.fieldN as old_valueN
from
a
,b
where a.primary_key = b.primary_key
)
set
old_value1 = new_value1
,old_value2 = new_value2
,....
,old_valueN = new_valueN;
--if the record is new to "A", then insert it into "B"
INSERT INTO B
SELECT *
FROM A
WHERE NOT EXISTS(
SELECT *
FROM B
WHERE B.PRIMARY_KEY = A.PRIMARY_KEY
);
