公司有AB两个库,前段时间想让B库和A库的部分表格同步,同步到B库的表主要用作报表统计,让后将统计结果保存到B中的其他表格中。

    领导建议我用物化视图做数据同步。后来通过查资料,发现物化视图确实是一个很不错的选择,配置简单、对主库的性能影响很小。
    物化视图虽然听着只是个视图,但是它确实是一个表格,通过USER_TABLES查询物化视图确实是一个真实存在的表。
 
下面讲讲我怎么通过物化视图做数据同步。
AB两个库都有tom用户,我们这里是将A库中的test表同步到B库中的tom用户下。

一、配置tnsnames.ora文件

指定到主数据库的网络名,这里是

 

 

二、在从数据库上创建dblink1.
给tom用户授予创建dblink的权利

grant create database link to tom;

 

2.创建dblink

conn tom/passwd

create database link link_db01_tom connect to tom identified by passwd using 'db01';

 

--link_db01_tom 是dblink的名字

--db01是网络名

 

3.测试db-link是否成功

select table_name from user_tables@link_db01_tom;

返回的行数与在master表的查询结果一样

 

 

三、在主数据库的表上创建物化视图日志,这个日志记录了AB两库test表的数据差别数量。

1.这里的主表名字是test

SQL> create materialized view log on test;

 

2.查看物化视图日志中记录的主表dml操作数量

select count(*) from MLOG$_TEST;

 

 

四、从数据库上创建物化视图

1.给用户tom授予create materialized view权限。

grant create materialized view to tom;

 

2.创建物化视图

create materialized view test refresh fast with primary key start with sysdate next sysdate+2/1440 as select * from test@link_db01_tom;

----

这里用的是fast自动更新,fast只更新主表变化的行;

with sysdate next sysdate+2/1440 --表示2分钟更新一次

----

 

五、测试

1、向A库tom用户下的test表插入一行

2、查看物化视图日志记录的DML操作数是否是1,如果是1,说明物化视图日志已经生效了

select count(*) from MLOG$_TEST;

3、2分钟后,查看B库的tom用户的test是否与A库的test表数据同步,

然后再看

select count(*) from MLOG$_TEST;

这个是否查询结果应该是0,说明AB库的test表数据已经一致。

 

六·物化视图删除的顺序


SQL> DROP MATERIALIZED VIEW LOG ON T1;

SQL> DROP MATERIALIZED VIEW MV_T1;

 

=====
注意:
如果是在一个库里做物化视图,
那么要将查询日志的权限给目标表的用户
grant select on t2.mlog$t_test to t1;