Materialized View for two-way synchronization of Oracle database tables

Materialized views implement two-way synchronization of Oracle database tables there are many ways to achieve cross-Database Synchronization of Oracle database tables, such as triggers, Materialized View (MV), Stream, www.2cto.com Materialized View (Materialized View), such as Goldengate, is a database object that includes query results. It is a local copy of remote data or is used to generate a summary table based on the sum of data tables. materialized views Store Data Based on Remote tables, also known as snapshots. this basically represents the essence of the materialized view, which is the result of a group of queries. This will inevitably greatly improve the query performance when this set of data is required again in the future. the following describes how to use Materialized View + Job to synchronize tables in two directions. The specific steps are as follows: www.2cto.com 1. create table [SQL] create table test (id varchar2 (10) not null primary key, name varchar2 (20), status varchar2 (1 ), updatedate date) 2. create dblink [SQL] create database link dblink_to_ B connect to "userid" identified by "password" using "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = "ipaddress") (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = "SID ")))'; create database link dblink_to_A connect to "userid" identified by "password" using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = "ipaddress ") (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = "SID") '; 3. create Materialized View and Materialized view log [SQL] create materialized view log on test with rowid create materialized view mv_test refresh fast on demand with rowid as select * from <A href = "mailto: test @ dblink_to_ B "> test @ dblink_to_ B </a> 4. create Materialized View and Materialized view log [SQL] create materialized view log on test with rowid create materialized view mv_test refresh fast on demand with rowid as select * from <a href = "mailto: test @ dblink_to_A "> test @ dblink_to_A </a> 5. during the creation of the stored procedure in the source database and target database, oracle 11g can support adding the where Condition for the update statement in merge into, but 9i does not support it. Therefore, if it is a 9i database, when the set statement is assigned a value, you can determine whether to update the value or the old value (decode method). However, the disadvantage is that all matched records will be updated; another method is to directly write an insert statement without merge to insert new values, and then write an update statement to update the latest record to be updated [SQL] dbms_mview.refresh ('mv _ test ', 'F'); select count (*) INTO v_count FROM mv_test where rownum <2; IF v_count> 0 then merge into test a USING (SELECT id, name, status, updatedate FROM mv_test) B ON (. id = B. id) when matched then update set. name = B. name,. status = B. status,. updatedate = B. updatedate where. updatedate <B. updatedate when not matched then insert (. id,. name,. status,. updatedate) VALUES (B. id, B. name, B. status, B. updatedate); COMMIT; end if; exception when others then rollback; 6. create a Job and regularly execute the created Stored Procedure PS: at the beginning, we also used triggers for two-way table synchronization, however, if the dblink of the two databases is broken, the data consistency will be affected. Therefore, Materialized view is used, but this method is also limited, first, you need a primary key field that cannot be modified when creating a table. Second, you cannot delete the record but can only identify the field to identify whether the record is valid.