根据网上资料以及自身实践,使用场景:
1:可以用于服务于应用读写分离
2:查询逻辑复杂,数据量比较大,导致每次查询视图或表的时候,查询速度慢,效率低下
操作步骤
一、授权
用system登录oracle,给你需要的用户user1授予(oracle中的用户对应表空间)(create any table、create any procedure)权限:
grant create any table to user1;
grant create any procedure to user1;
二、在目标库创建 dblink ,指向源数据库:
先授权:
grant create database link to user1;
然后创建dblink 连接的服务实例为orcl,指定的源数据库地址192.168.xxxx.xxxx,源数据库用户名密码:source_name/source_pwd,默认端口号1521
create public database link test_link1 connect to source_name IDENTIFIED BY source_pwd
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xxxx.xxxx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';
三、在源数据库上执行创建物化视图日志:需要用普通具体的用户登录cmd窗口下: sqlplus source_name/source_pwd 或者再oracle管理员用户下 conn XXGL_FORMA/XXGL_FORMA
CREATE MATERIALIZED VIEW LOG ON 源数据库某表名 WITH Rowid;
物化视图的复制默认是基于主键的,也可以基于rowid,这里是create materialized view log on (主表名) with rowid,这样创建物化视图就要对应的加上with rowid
如果这一步省略了,会报错
第 1 行出现错误:
ORA-23413: 表 "XXX"."aa" 不带实体化视图日志,或者物化视图的刷新机制为complete别用增量更新,
即第四部用这个:
CREATE MATERIALIZED VIEW MV_物化视图名字 complete with rowid NEXT NEXT_DAY(TRUNC(SYSDATE), '星期日')+1/24 AS SELECT * FROM 源数据库某表名@test_link1;
四、在目标库上执行:需要用普通具体的用户登录 sqlplus target_name/target_pwd 每周日凌晨01:00执行从源数据库中抽取数据
CREATE MATERIALIZED VIEW MV_物化视图名字 REFRESH FAST with rowid NEXT NEXT_DAY(TRUNC(SYSDATE), '星期日')+1/24 AS SELECT * FROM 源数据库某表名@test_link1;
默认是不可更新的,如果创建可以更新的物化视图得加上for update
CREATE MATERIALIZED VIEW MV_物化视图名字 REFRESH FAST with rowid NEXT NEXT_DAY(TRUNC(SYSDATE), '星期日')+1/24 for update AS SELECT * FROM 源数据库某表名@test_link1;
-----------------------------------分割线--------------------------------------------------
查询物化视图上次刷新时间
SELECT last_refresh_date
FROM user_mviews
WHERE mview_name = 'MV_物化视图名字';
手动刷新物化视图
BEGIN
dbms_mview.refresh('MV_物化视图名字');
END;
删除物化视图的话,可以使用如下语句
DROP MATERIALIZED VIEW MV_物化视图名字;
删除实体化视图日志
drop materialized view log on table1;
更多资料详见:
ORACLE读写分离 https://blog.csdn.net/qq_20544709/article/details/80883571
ORACLE-数据抽取及备份https://blog.csdn.net/yangwenxue_admin/article/details/51863359
刷新方式:https://blog.csdn.net/minwang593/article/details/19107803