物化视图实现 Oracle 数据库表双向同步

 Oracle 数据库跨库同步表有很多种方式可以实现, 比如触发器, Materialized View（MV）, Stream, Goldengate 等

       Materialized View(物化视图)是包括一个查询结果的数据库对像, 它是远程数据的的本地副本, 或者用来生成基于数据表求和的汇总表. 物化视图存储基于远程表的数据, 也可以称为快照. 这个基本上就说出了物化视图的本质, 它是一组查询的结果, 这样势必为将来再次需要这组数据时大大提高查询性能.下面就介绍使用 Materialized View + Job 的方式来双向同步表,具体步骤如下:

        1. 在源数据库 A 和目标数据库 B 上分别建立 table      

[sql]  view plain copy print ?

1. create table test   
2. (  
3.     id varchar2(10) not null primary key,  
4.     name varchar2(20),  
5.     status varchar2(1),  
6.     updatedate date  
7. )  

 

        2. 在数据库上分别建立 dblink

[sql]  view plain copy print ?

1. create database link dblink_to_B   
2. connect to "userid" identified by "password"  
3. using '(DESCRIPTION =  
4.     (ADDRESS_LIST =  
5.         (ADDRESS = (PROTOCOL = TCP)(HOST = "ipaddress")(PORT = 1521))  
6.     )  
7.     (CONNECT_DATA =  
8.         (SERVICE_NAME = "SID")  
9.     )  
10. )';  
11.   
12. create database link dblink_to_A   
13. connect to "userid" identified by "password"  
14. using '(DESCRIPTION =  
15.     (ADDRESS_LIST =  
16.         (ADDRESS = (PROTOCOL = TCP)(HOST = "ipaddress")(PORT = 1521))  
17.     )  
18.     (CONNECT_DATA =  
19.         (SERVICE_NAME = "SID")  
20.     )  
21. )';  

       

         3. 在源数据库 A 上建立 Materialized View 以及 Materialized view log

[sql]  view plain copy print ?

1. create materialized view log on test with rowid  
2.      
3. create materialized view mv_test refresh fast on demand with rowid   
4. as select * from <a href="mailto:test@dblink_to_B">test@dblink_to_B</a>  

 

        4. 在目标数据库 B 上建立 Materialized View 以及 Materialized view log

[sql]  view plain copy print ?

1. create materialized view log on test with rowid  
2.      
3. create materialized view mv_test refresh fast on demand with rowid   
4. as select * from <a href="mailto:test@dblink_to_A">test@dblink_to_A</a>   

 

        5. 在源数据库和目标数据库创建存储过程, oracle 11g  可以支持 merge into 中 update 语句增加 where 条件, 但是 9i 就不支持, 所以如果是 9i 的数据库, 就可以在 set 语句赋值的时侯进行判断是否更新值还是旧值 （decode 的方式）, 但是缺点就是会所有 matched 的记录都会更新；另外一种方式就是不用 merge, 直接写一个 insert 语句插入新增的值, 再写一个 update 语句更新最新的需要更新的记录

[sql]  view plain copy print ?

1. dbms_mview.refresh('mv_test','F');  
2. SELECT COUNT(*) INTO v_count  
3. FROM mv_test  
4. WHERE ROWNUM < 2;  
5.   
6. IF v_count > 0 THEN  
7.     MERGE INTO test a  
8.     USING (SELECT id, name, status, updatedate  
9.     FROM mv_test) b  
10.     ON (a.id = b.id)  
11.     WHEN MATCHED THEN  
12.         UPDATE  
13.         SET a.name = b.name, a.status = b.status, a.updatedate = b.updatedate  
14.         where a.updatedate < b.updatedate  
15.     WHEN NOT MATCHED THEN  
16.         INSERT (a.id, a.name, a.status, a.updatedate)  
17.         VALUES (b.id, b.name, b.status, b.updatedate);  
18.     COMMIT;  
19. END IF;  
20. EXCEPTION  
21.     WHEN OTHERS THEN  
22.     rollback;  

        6. 分别创建 Job 定期执行创建的存储过程      

        PS： 最开始的时候也做过用触发器的方式进行双向同步表, 可万一两台数据库的dblink断了就会影响数据的一致性, 所以还是决定使用 Materialized view 的方式, 但是这种方式也是有局限, 首先建立表的时候需要一个不能修改的主键字段, 其次不能删除记录而只能标识字段的方式标识记录是否有效