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

最新推荐文章于 2024-07-28 18:13:50 发布

weixin_34401479

最新推荐文章于 2024-07-28 18:13:50 发布

阅读量305

点赞数

文章标签：数据库

本文详细介绍了在Oracle数据库中创建表、物化视图，并通过数据链实现源端和目标端的数据同步过程。从源端创建表及物化视图开始，到目标端建立数据链并创建刷新型物化视图，最后进行数据同步测试，验证了数据能够实时更新至目标端。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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