[20150610]使用物化视图同步数据.txt
--昨天听别人的一个需求要同步一个表的数据,要求使用golden gate有点小题大作。实际上物化事务就可以了，自己以前做过一些测试，也
--许没做记录，这次做一个记录。
1.建立测试环境：
--源数据库10g 10.2.0.4.0 IP=192.168.100.89
--同步表T。
create table t ( id number CONSTRAINTS pk_t primary key , name varchar2(20));
insert into t select rownum,'test' from dual connect by levelcommit ;
--目的数据库11g 11.2.0.3.0 . IP=192.168.100.40
CREATE PUBLIC DATABASE LINK "TEST089.COM" CONNECT TO SCOTT IDENTIFIED BY btbtms USING '192.168.100.89:1521/test';
--建立一样的表：
create table t ( id number CONSTRAINTS pk_t primary key , name varchar2(20));
SCOTT@test> select * from t@test089.com where rownum=1;
ID NAME
---------- --------------------
1 test
--测试dblink通过。
2.测试同步：
--源数据库10g 10.2.0.4.0
create materialized view log on t;
--目的数据库11g 11.2.0.3.0 .
create materialized view t on prebuilt table refresh fast start with sysdate next sysdate + 1/24/60 as select * from t@test089.com;
3.开始一次测试：
--源数据库10g 10.2.0.4.0 做一些dml操作看看：
insert into t values (11,'aaa');
commit ;
update t set name='xxx' where id=5;
commit ;
delete from t where id=2;
commit ;
SCOTT@test> select * from t;
ID NAME
------------ --------------------
1 test
3 test
4 test
5 xxx
6 test
7 test
8 test
9 test
10 test
11 aaa
10 rows selected.
--等1分钟观察目的数据库情况：
--目的数据库11g 11.2.0.3.0 .
SCOTT@test> select * from t;
ID NAME
---------- --------------------
11 aaa
5 xxx
--搞错，应该copy数据先，重做。
drop materialized view t ;
delete from t;
commit ;
SCOTT@test> select * from t;
no rows selected
SCOTT@test> insert into t select * from t@test089.com;
10 rows created.
SCOTT@test> commit ;
Commit complete.
create materialized view t on prebuilt table refresh fast start with sysdate next sysdate + 1/24/60 as select * from t@test089.com;
4.再重复测试：
--源数据库10g 10.2.0.4.0 做一些dml操作看看：
insert into t values (12,'bbb');
commit ;
update t set name='yyy' where id=5;
commit ;
delete from t where id=3;
commit ;
SCOTT@test> select * from t;
ID NAME
------------ --------------------
1 test
4 test
5 yyy
6 test
7 test
8 test
9 test
10 test
11 aaa
12 bbb
10 rows selected.
--等1分钟观察目的数据库情况：
--目的数据库11g 11.2.0.3.0 .
SCOTT@test> select * from t;
ID NAME
---------- --------------------
1 test
4 test
5 yyy
6 test
7 test
8 test
9 test
10 test
11 aaa
12 bbb
10 rows selected.
SCOTT@test> select * from t minus select * from t@test089.com;
no rows selected
SCOTT@test> select * from t@test089.com minus select * from t;
no rows selected
通过视图可以观察同步的情况：
--目的数据库11g 11.2.0.3.0 :
SCOTT@test> SELECT JOB, LOG_USER, LAST_DATE, NEXT_DATE, FAILURES FROM USER_JOBS;
JOB LOG_USER LAST_DATE NEXT_DATE FAILURES
---------- ------------------------------ ------------------- ------------------- ----------
743 SCOTT 2015-06-10 09:20:29 2015-06-10 09:21:29 0