使用物化视图的方式进行表级数据同步示例

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                 &