Oracle基于物化视图的远程数据复制

物化视图简介：

远程表复制功能：可以借助数据库链接（dblink），在远程数据库中建立一个本地表的副本,用该方式实现表的定时同步。物化视图存储基于远程表的数据，也可以称为快照。

加速查询功能：物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，在执行查询时，可以直接查询物化视图，或者通过查询重写定位到物化视图，来加快速度。

本文使用物化视图的远程表复制功能。

源端配置：

目标端配置：

源端搭建

1.创建表空间

SQL> conn / as sysdba Connected. SQL> CREATE TABLESPACE "TS_MV_TEST" LOGGING DATAFILE '/orasvr/whbkdb/ts_mv_test/TS_MV_TEST01.dbf ' SIZE 512M AUTOEXTEND ON NEXT  128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO; Tablespace created.

表空间名为TS_MV_TEST

2.创建用户

CREATE USER USER_MV IDENTIFIED BY USER_MV DEFAULT TABLESPACE TS_MV_TEST PROFILE DEFAULT ACCOUNT UNLOCK; GRANT CONNECT TO USER_MV; GRANT RESOURCE TO USER_MV; GRANT CREATE DATABASE LINK TO USER_MV; GRANT CREATE MATERIALIZED VIEW TO USER_MV; ALTER USER USER_MV QUOTA UNLIMITED ON TS_MV_TEST;

创建用户USER_MV

3.创建表

SQL> conn user_mv/user_mv; Connected. SQL> create table tt(id int,name varchar2(30)); Table created.

使用user_mv用户登陆，创建表tt

4.新建索引和约束

create unique index PK_tt on tt (id) tablespace TS_MV_TEST; ALTER TABLE tt ADD (  CONSTRAINT PK_tt  PRIMARY KEY  (ID)  USING INDEX PK_tt  ENABLE VALIDATE);

新建唯一索引pk_tt和约束pk_tt

5.创建物化视图日志

SQL> CREATE MATERIALIZED VIEW LOG ON tt with primary key; Materialized view log created.

创建物化视图日志

图片.png

创建物化视图日志的同时会生成表MLOG$_TT，当使用primary key时，oracle创建临时表 RUPD$_基础表。

目标端搭建

1.创建表空间

SQL> CREATE TABLESPACE "TS_BK" LOGGING DATAFILE '/orasvr/whbkdb/ts_bk/TS_BK01.dbf ' SIZE 512M AUTOEXTEND ON NEXT  128M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO; Tablespace created.

创建表空间TS_BK

2.创建用户

CREATE USER USER_BK IDENTIFIED BY USER_BK DEFAULT TABLESPACE TS_BK PROFILE DEFAULT ACCOUNT UNLOCK; GRANT CONNECT TO USER_BK; GRANT RESOURCE TO USER_BK; GRANT CREATE DATABASE LINK TO USER_BK; GRANT CREATE MATERIALIZED VIEW TO USER_BK; ALTER USER USER_BK QUOTA UNLIMITED ON TS_BK;

创建用户user_bk

3.创建dblink

SQL> conn  user_bk/user_bk; Connected. SQL> create database link mv connect to user_mv identified by user_mv using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.27.9.55 )(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = whbkdb) ) )'; Database link created.

使用user_bk登陆，创建dblink mv

4.创建物化视图

CREATE MATERIALIZED VIEW mv_bk BUILD IMMEDIATE REFRESH FORCE ON DEMAND next sysdate 1/2880 with primary key AS SELECT * from user_mv.tt@mv;

创建物化视图mv_bk，手工方式刷新，sysdate 1/2880表示每半分钟刷新一次

图片.png

可以看到在创建物化视图的同时会新建同名表mv_bk

测试

源端插入测试数据

SQL> insert into tt values(1,'A'); 1 row created.

查看目标端物化视图mv_bk

SQL> select * from mv_bk;        ID NAME ---------- ------------------------------        1 A

源端清空表数据

SQL> delete from tt;

查看目标端物化视图

SQL> select * from mv_bk; no rows selected

至此Oracle基于物化视图的远程数据复制搭建测试完成，本文搭建脚本下载