Oracle物化视图实现本地定时访问远程数据库的数据

需求描述：
近日，公司研发部门要求实时访问生产库的数据，但是由于考虑到生产库环境的压力，实时更新调整为每隔5分钟刷新一下数据。通过Oracle的物化视图功能是实现该需求。

具体测试验证步骤如下：

##远端创建测试用户和数据
create user utest identified by utest;
grant connect,resource to utest;

create table tab1(tid int primary key,uname varchar2(10));
insert into tab1 values(1,'oracle');
insert into tab1 values(2,'oracle2');

insert into tab1 values(3,'test1');
insert into tab1 values(4,'test2');

##目标端创建测试用户
create user utarget identified by utarget;
grant connect,resource to utarget;

grant create public database link to utarget;

##目标端创建dblink访问远程数据
create public database link utest_dbl connect to utest identified by "utest" USING 'SCIMS_STD';

##测试验证dblink是否创建成功
select * from tab1@utest_dbl

##具体实施步骤如下
--1、给本地库授权
grant create materialized view to utarget;

--2、源系统库目标表建日志
/*
方式1：通过primary key
create materialized view log on tab1  
tablespace movebi_data -- 日志空间  
with primary key;     -- 指定为主键类型无主键可指定rowid,但注意要和后面创建物化视图时保持一致

drop materialized view log on tab1;
*/

create materialized view log on tab1  
tablespace USERS
with rowid;


--查看日志
select * from mlog$_tab1;

--3、本地库创建物化视图
/*
可选方式1：通过primary key
create materialized view mv_tab1
refresh force
on demand
start with sysdate next sysdate+5/24/60
as
select * from tab1@utest_dbl;
*/

可选方式2：通过rowid，如果表没有主键的话，可以通过rowid
create materialized view mv_tab1
refresh fast with rowid
on demand
start with sysdate next sysdate+2/24/60
as
select * from tab1@utest_dbl;

--refresh fast with rowid  快速刷新
--on demand  在用户需要时由用户刷新
--start with sysdate next sysdate+5/24/60  这个物化视图每5min进行刷新
--disable query rewrite  不可重写（默认）

/*
force（默认）自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

##物化视图有二种刷新模式
on demand 顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性;
on commit 提交触发，一旦基表有了commit，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。一般用这种方法在操作基表时速度会比较慢。
##三种刷新方法
完全刷新（COMPLETE）： 会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
快速刷新（FAST）： 采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
FORCE 方式：这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

关于快速刷新：Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。
物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

*/

--4、修改刷新时间
alter materialized view mv_dept_tmp_test refresh force on demand start with sysdate next sysdate+10/24/60
--5、手动快速刷新
begin
 DBMS_MVIEW.REFRESH(LIST => 'v_union_target_bak',METHOD => 'f');
end;
--6、目标库删除物化视图日志
drop materialized view log on tab1;
--7、本地库删除物化视图
drop materialized view mv_tab1;

##查看物化视图刷新状态信息
select mview_name, last_refresh_date, staleness from dba_mviews;

select name, last_refresh from dba_mview_refresh_times;

staleness：
1、fresh 最新状态：当前物化视图的内容出于最新的状态
2、stale 陈旧状态：物化视图引用的主表已经更新，但是物化视图没有刷新，所以内容相对主表来说是旧的
3、needs_compile 需要编译：物化视图引用的主表比如视图，进行了重建后相应的物化视图就需要编译，当处于这种状态的时候dba_objects
视图显示的STATUS为INVALID需要运行语句:alter materialized view mv_name compile;
4、unusable 物化视图引用的主表状态不确定
5、unknown 未知：通过prebuilt创建的表
6、undefined 物化视图引用的表来自其他的数据库，一般通过dblink链接过来的