创建物化视图并定时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
  • 13561

Oracle 物化视图使用

一、物化视图用法:物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句...
  • lichangzai
  • lichangzai
  • 2016年04月22日 14:07
  • 8574

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

对于oracle10g简单方法:1. 先建立一个物化视图,不用指明刷新参数,只要create materialized view,如  CREATE MATERIALIZED VIEW mv_emp ...
  • jasontome
  • jasontome
  • 2011年07月13日 16:23
  • 1046

物化视图定时刷新之trunc运用

创建物化视图test_view,并每隔一小时刷新一次数据 [sql] view plain copy   create materialized view t...
  • zhangss723
  • zhangss723
  • 2016年07月19日 15:49
  • 217

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

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

DBMS_REFRESH

DBMS_REFRESH enables you to create groups of materialized views that can be refreshed together to a ...
  • lively1982
  • lively1982
  • 2013年11月12日 10:27
  • 2135

psql物化视图自动更新

更新物化视图示例CREATE TABLE model.test ( id SERIAL PRIMARY KEY, name VARCHAR(60) );. 创建物化视图CREATE MATER...
  • qq_27657429
  • qq_27657429
  • 2017年08月03日 18:40
  • 162

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

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

在dbms_mview.refresh中启用并行刷新

我们常常利用物化视图来同步数据,或者迁移数据。在dbms_mview中的刷新过程,可以使用并行刷新的特性,可以有效的减少完全刷新的时间,下面是一个过程示例: create materialized ...
  • e_wsq
  • e_wsq
  • 2011年12月01日 22:16
  • 2625

Oracle-Materialized View解读

物化视图概述物化视图(Materialized View)在9i以前的版本叫做快照(SNAPSHOT),从9i开始改名叫做物化视图。Oracle的物化视图是包括一个查询结果的数据库对像,它是远程数据的...
  • yangshangwei
  • yangshangwei
  • 2016年11月25日 00:12
  • 4326
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:创建物化视图并定时refresh
举报原因:
原因补充:

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