物化视图简介:
远程表复制功能: 可以借助数据库链接 (dblink), 在远程数据库中建立一个本地表的副本, 用该方式实现表的定时同步. 物化视图存储基于远程表的数据, 也可以称为快照.
加速查询功能: 物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果, 在执行查询时, 可以直接查询物化视图, 或者通过查询重写定位到物化视图, 来加快速度.
本文使用物化视图的远程表复制功能.
源端配置:
ip | 172.27.9.55 |
Oracle version | 11.2.0.4 |
sid | whbkdb |
user | user_mv |
tablespace | TS_MV_TEST |
table | tt |
MATERIALIZED VIEW LOG | MLOG$_T1 |
目标端配置:
ip | 172.27.9.56 |
Oracle version | 11.2.0.4 |
sid | whbkdb |
user | user_mbk |
tablespace | TS_BK |
dblink | mv |
MATERIALIZED VIEW | mv_bk |
源端搭建
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 用户登 6, 创建表 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 登 6, 创建 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 基于物化视图的远程数据复制搭建测试完成, 本文搭建脚本下载
来源: http://www.linuxidc.com/Linux/2018-09/153986.htm