Oracle 通过物化视图跨库同步表数据测试

Oracle 通过物化视图跨库同步表数据测试

在源库上存在一张百万级的表sr_rr_case_attach，需要在远程数据库上进行同步

源库：
1、源库上的表确认行数

SQL> select count(*) from sr_rr_case_attach;

  COUNT(*)
----------
   1971807

2、在源库表上检查是否有主键，如无添加

SQL> alter table sr_rr_case_attach modify(rowguid primary key);

3、创建物化视图日志表，用于增量更新

SQL> create materialized view log on sr_rr_case_attach; 

Materialized view log created.

目标库：
1、在目标库创建dblink连接源库

SQL> create public database link con209 connect to SUNRIGHT_ORA8 identified by "11111" using '192.168.189.209:1522/ora11g';

Database link created.

2、创建表结构

SQL> create table sr_rr_case_attach as select * from sr_rr_case_attach@con209 where 1=2;

Table created.

SQL> select count(*) from sr_rr_case_attach;

  COUNT(*)
----------
         0

3、创建物化视图

SQL> create materialized view sr_rr_case_attach on prebuilt table refresh fast as select * from sr_rr_case_attach@con209;

Materialized view created.

SQL> select count(*) from sr_rr_case_attach;

  COUNT(*)
----------
         0

4、完全刷新物化视图

 SQL> set timing on
SQL> exec dbms_mview.refresh('SR_RR_CASE_ATTACH','C');

PL/SQL procedure successfully completed.

Elapsed: 00:00:25.83
数据已同步
SQL> select count(*) from sr_rr_case_attach;

  COUNT(*)
----------
   1971807

5、在源库插入一行

   Insert into SR_RR_CASE_ATTACH values (null,null,null,1185,null,'8e095651-cdce-466e-8230-be1a643ffaee','7a973efe-2a6d-46d8-85aa-24844da473a1','320321JSXK2018080001','320321JS',null,'50a57d12-6d24-4a08-8a07-332c569b1c55','1',null,to_date('27-AUG-18','DD-MON-RR'));

6、目标库手动更新，增量更新同步

  SQL> exec dbms_mview.refresh('SR_RR_CASE_ATTACH','F');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.97
SQL> select count(*) from sr_rr_case_attach;

  COUNT(*)
----------
   1971808

7、删除物化视图

   SQL> drop materialized view sr_rr_case_attach;

Materialized view dropped.

Elapsed: 00:00:00.45
SQL> select count(*) from sr_rr_case_attach;

  COUNT(*)
----------
   1971807

物化视图删除之后基表数据没有影响，数据已同步