使用物化视图的方式进行表级数据同步示例
使用物化视图的方式进行表级数据同步示例
1.源端创建表及物化视图
BYS@bys1>conn bys/bys
Connected.
BYS@bys1>select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
BYS DBA NO YES NO
创建表及物化视图
BYS@bys1>create table test5(a int primary key);
Table created.
BYS@bys1>create materialized view log on test5;
Materialized view log created.
BYS@bys1>select * from tab where tname like '%TEST5%';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST5 TABLE
RUPD$_TEST5 TABLE
MLOG$_TEST5 TABLE
################################################
2.目标端：创建数据链
SYS@bys2>conn bys/bys
Connected.
BYS@bys2>select * from user_role_privs;
USERNAME GRANTED_ROLE ADMIN_ DEFAUL OS_GRA
---------- --------------- ------ ------ ------
BYS DBA NO YES NO
BYS@bys2>create database link bys1 connect to bys identified by bys using 'bys1';
Database link created.
BYS@bys2>create materialized view test5 refresh fast start with sysdate next sysdate+1/(1440*60) with primary key as select * from test5@bys1;
Materialized view created.
BYS@bys2>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
TEST TABLE
TEST5 TABLE
BYS@bys2>select * from test5;
no rows selected
BYS@bys2>desc test5;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
A NOT NULL NUMBER(38)
###################################
3.数据同步测试：
源端：
BYS@bys1>set time on
14:12:09 BYS@bys1>select * from test5;
no rows selected
14:12:27 BYS@bys1>insert into test5 values(111);
1 row created.
14:12:36 BYS@bys1>commit;
Commit complete.
14:12:37 BYS@bys1>select * from test5;
A
----------
111
目标端：
14:12:16 BYS@bys2>select * from test5;
no rows selected
14:12:18 BYS@bys2>
14:12:45 BYS@bys2>select * from test5;
A
----------
111
14:12:46 BYS@bys2>s
