The question of why Materialized Views with Refresh ON COMMIT cannot be created across databases (in effect providing Synchronous Replication) has come up a few times on the web.
It came up again in this forums posting.
This is my response :
MultiMasterAdvancedReplication works on a PUSH method.
You insert into Table "T_A" in database "D_A" and oracle puts the transaction in the queue tables in "D_A" to be propagated to "D_B". They are then "pushed" from "D_A" to "D_B" via a DBLink. If you option for Synchronous, the "push" is immediate with the transaction on "T_A" and becomes a "2-Phase Commit".
MaterializedViews across DBLinks use a PULL method.
An MV "MV_A" in database "D_B" "pulls" data from "T_A" and "T_A"'sSnapshotLog via a DBLink from "D_B" to read data from "D_A". There is no DBLink from "D_A" to "D_B".
Therefore, a transaction that inserts into "T_A" has no way of pushing the data to "D_B" because there is no DBLink. Therefore it is not possible to have Synchronous MVs across Databases.
If the MV "MV_A" built to REFRESH ON COMMIT is in the same database "D_A" then the transaction inserting into "T_A" merely has to update "MV_A" in the same database -- it just becomes a transaction on *two* *LOCAL* tables inside of "D_A".