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
物化视图删除之后基表数据没有影响,数据已同步