创建物化视图并定时refresh

原创 2012年03月27日 19:36:28

当我们有报表服务器等系统,为了性能的原因很多时候会使用物化视图,为了保持mview与基表的同步,需要refresh mview.mview的refresh可以分为on commit,on demand,never几种,使用db link的mview不能采用on commit的refresh方式
通常mview根本不需要updateable,所以使用高级复制显然增加了复杂程度,所以就只剩下on demand方式了。需要调用过程dbms_mview.refresh来完成。

我们可以使用某些方式来定时refresh mview.

环境:
orcl01 (主,hostname dg1,用户schema:scott)
orcl02 (mview site,hostname dg2,用户schema:hr)

1.在orcl01上创建materialized view log (fast refresh 需要rowid)
[oracle@dg1 ~]$ sqlplus scott/scott
SQL> create MATERIALIZED VIEW LOG ON emp with rowid;
SQL> create MATERIALIZED VIEW LOG ON dept with rowid;

2.在orcl02上
[oracle@dg2 ~]$ sqlplus / as sysdba
2.1 Hr 用户的权限:
SQL> grant CREATE MATERIALIZED VIEW to hr;
SQL> select * from dba_sys_privs where grantee ='HR' ;

GRANTEE    PRIVILEGE                   ADM
---------- --------------------------- ---
HR         CREATE VIEW                 NO
HR         CREATE MATERIALIZED VIEW    NO
HR         UNLIMITED TABLESPACE        NO
HR         CREATE DATABASE LINK        NO
HR         CREATE SEQUENCE             NO
HR         CREATE SESSION              NO
HR         ALTER SESSION               NO
HR         CREATE SYNONYM              NO

2.2 创建并测试db link (listener,tnsnames.ora已经配置好)
[oracle@dg2 ~]$ sqlplus hr/hr
SQL> create database link priv_link_orcl01_scott connect to scott identified by scott using 'orcl01';
SQL> select count(*) from
emp@priv_link_orcl01_scott;

COUNT(*)
----------
        14

2.3 创建物化视图
SQL> create materialized view testmv_emp_dept
2 refresh fast on demand
3 as
4 select e.rowid "emp_rowid",d.rowid "dept_rowid",e.ename,d.dname,d.loc
5      from emp@priv_link_orcl01_scott e, dept@priv_link_orcl01_scott d
6      where e.deptno = d.deptno;

Materialized view created.

2.4 测试mview
1)在orcl01上:
[oracle@dg1 ~]$ sqlplus scott/scott
SQL> update emp set ename='CLARKCLARK' where ename='CLARK';

1 row updated.

SQL> commit;

Commit complete.

因为materialized view 创建的是on demand的,所以上面的修改并不会自己refresh到Orcl02上,需要执行dbms_mview.refresh:
2)在orcl02上
[oracle@dg2 ~]$ sqlplus hr/hr
SQL> exec DBMS_MVIEW.REFRESH (list=>'TESTMV_EMP_DEPT');

PL/SQL procedure successfully completed.

(默认情况下dbms_mview.refresh的参数purge_option=1,意思是:refresh完成mview log会被purge
这个在orcl01上查询mview log可以看到:
SQL> select * from MLOG$_EMP;
no rows selected)

SQL> select * from testmv_emp_dept;

emp_rowid                        dept_rowid                          ENAME        DNAME             LOC
-----------------------------    -------------------------------- ------------- ------------------   -------------
AAAMgJAAEAAAAAgAAI   AAAMgHAAEAAAAAQAAA KINGKING   ACCOUNTING     NEW YORK
AAAMgJAAEAAAAAgAAN AAAMgHAAEAAAAAQAAA MILLER      ACCOUNTING     NEW YORK
AAAMgJAAEAAAAAgAAD AAAMgHAAEAAAAAQAAB JONES       RESEARCH         DALLAS
AAAMgJAAEAAAAAgAAM AAAMgHAAEAAAAAQAAB FORD        RESEARCH         DALLAS
AAAMgJAAEAAAAAgAAK AAAMgHAAEAAAAAQAAB ADAMS       RESEARCH        DALLAS
AAAMgJAAEAAAAAgAAA AAAMgHAAEAAAAAQAAB SMITH        RESEARCH        DALLAS
AAAMgJAAEAAAAAgAAH AAAMgHAAEAAAAAQAAB SCOTT       RESEARCH        DALLAS
AAAMgJAAEAAAAAgAAC AAAMgHAAEAAAAAQAAC WARD        SALES               CHICAGO
AAAMgJAAEAAAAAgAAJ AAAMgHAAEAAAAAQAAC TURNER     SALES               CHICAGO
AAAMgJAAEAAAAAgAAB AAAMgHAAEAAAAAQAAC ALLEN        SALES               CHICAGO
AAAMgJAAEAAAAAgAAL AAAMgHAAEAAAAAQAAC JAMES        SALES              CHICAGO
AAAMgJAAEAAAAAgAAF AAAMgHAAEAAAAAQAAC BLAKE        SALES              CHICAGO
AAAMgJAAEAAAAAgAAE AAAMgHAAEAAAAAQAAC MARTIN       SALES              CHICAGO
AAAMgJAAEAAAAAgAAG AAAMgHAAEAAAAAQAAA CLARKCLARK   ACCOUNTING     NEW YORK

14 rows selected.
显然orcl01的修改已经refresh到orcl02上的mview.

3.定时refresh
为了定时执行refresh,有几种方式:
1) dbms_job来定时调用exec DBMS_MVIEW.REFRESH (list=>'TESTMV_EMP_DEPT');
2) dbms_refresh来执行
3) unix cron定时执行一个shell 由它来调用sqlplus 执行exec DBMS_MVIEW.REFRESH (list=>'TESTMV_EMP_DEPT');

下面只介绍2. dbms_refresh

dbms_refresh的说明:
DBMS_REFRESH enables you to create groups of materialized views that can be refreshed together
to a transactionally consistent point in time.
优势:分组、事务一致性

创建refresh group
SQL> BEGIN
2     DBMS_REFRESH.MAKE (
3        name => 'hr_refg',
4        list => 'TESTMV_EMP_DEPT',
5        next_date => SYSDATE,
6        interval => 'SYSDATE + 1/288');
7 END;
8 /

PL/SQL procedure successfully completed.

上面的过程会创建refresh group,并且添加定时执行等信息到job中添加的job:
SQL> select job,log_user,to_char(next_date,'DD-MON-YYYY HH24:MI:SS') next_date,
2 interval,what from user_jobs;


JOB LOG_USER   NEXT_DATE            INTERVAL              WHAT
------ ---------- --------------------------       --------------------    ----------------------------------------
       1 HR         02-APR-2008 16:54:15   SYSDATE + 1/288 dbms_refresh.refresh('"HR"."HR_REFG"');

refresh group:
SQL> select rowner,rname,job from user_refresh;

ROWNER                         RNAME                                 JOB
------------------------------ ------------------------------ ----------
HR                             HR_REFG                                 1       

refresh group中的object:
SQL> select owner,name,rname from user_refresh_children;

OWNER     NAME               RNAME
--------- ------------------ ----------
HR        TESTMV_EMP_DEPT    HR_REFG

如果还需要添加其他的物化试图则使用下面的例子:
BEGIN
   DBMS_REFRESH.ADD (
      name => 'hr_refg',
      list => 'hr.employees_mv2';
END;
/

物化视图每天自动刷新2次的方法

有用过ORCLE 物化视图的朋友,请帮个忙,我想每天刷新两次,比如中午十二点,晚上七点,不知道怎么写,急用!你这个需要,可以这样实现 建一个表,再写个存储过程 再用个SCHEDULE JOB来实现 用...
  • outget1
  • outget1
  • 2010年02月03日 11:51
  • 13112

物化视图创建 及 刷新机制修改

Oracle物化视图语法2010-05-21 09:58    物化视图对于前台数据库使用者来说如同一个实际的表,具有和一般表相同的如select等操作,而其实际上是一个视图,一个由系统实现定期刷新其...

fast物化视图的刷新方式

一. 物化视图概述 Oracle的物化视图是包括一个查询结果的数据库对像,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。 物化视图...

DBMS_REFRESH

DBMS_REFRESH enables you to create groups of materialized views that can be refreshed together to a ...

创建自动、增量更新的物化视图

创建定期增量刷新物化视图   2012-01-16 10:34:57|  分类: ORACLE工程实施|举报|字号 订阅 1,在源库建立被同步表的物化视图日志:...
  • hanbowu
  • hanbowu
  • 2014年10月19日 19:41
  • 1408

Oracle 物化视图1 - 单表聚合及其快速刷新

简介 物化视图在数据仓库中常用,将结果预先计算好并存储在物化视图中,Oracle数据库通过Query Rewrite访问物化视图。可以提高SQL反应速度,改善用户体验。整个过程对用户是透明的。对于每个...

rman全库备份,恢复过程

最近一直在忙于写shell脚本,忽略了oracle的基础的回顾,正好做了个rman的恢复,记录一下加深印象! oracle全库备份的理想状态下在mount时做全备,这样不需要去做考虑一致性问题。仅仅...

关于Oracle数据库的用户对象配置以及数据镜像的总结

主题:            1. 配置表空间和各种用户对象。            2. 建立从一个实时数据库到一个备份数据库的数据镜像。 环境:            Sun Solaris Un...
  • yorck
  • yorck
  • 2004年07月23日 10:14
  • 1965

oralce创建物化视图

  • 2014年04月23日 09:16
  • 314B
  • 下载

物化视图定时指定时间点刷新

对于oracle10g简单方法:1. 先建立一个物化视图,不用指明刷新参数,只要create materialized view,如  CREATE MATERIALIZED VIEW mv_emp ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:创建物化视图并定时refresh
举报原因:
原因补充:

(最多只允许输入30个字)