ITPub博客

首页 > IT基础架构 > 网络安全 > 物化视图(Materialized View)的刷新回滚测试

物化视图(Materialized View)的刷新回滚测试

原创 网络安全 作者:zhyuh 时间:2005-01-24 14:05:50 0 删除 编辑

物化视图在完全刷新(complete refresh)过程中,由于各种原因而刷新失败时,会导致该物化视图中的记录数为0。这是由于对单个物化视图的完全刷新,Oracle采用truncate/insert方法。要避免这种情况,使物化视图能在刷新失败时保持在刷新前的状态,可以采用刷新物化视图组(Materialized View Group)的方式。这是因为Oracle对物化视图组的刷新采用delete/insert方法。

[@more@]

==创建2个MV
SQL>      create materialized view dept_mv
  2       build immediate
  3       refresh complete
  4       on demand
  5       enable query rewrite
  6       as
  7       select * from dept;

Materialized view created

SQL>      create materialized view emp_mv
  2       build immediate
  3       refresh complete
  4       on demand
  5       enable query rewrite
  6       as
  7       select empno, ename,sal from emp;

Materialized view created

==创建materialized view group,包含刚才创建的2个MV
SQL> execute dbms_refresh.make(name => 'group1', list => 'emp_mv,dept_mv', next_date => sysdate, interval => 'sysdate+1/48', implicit_destroy => true);

PL/SQL procedure successfully completed

==执行complete refresh并跟踪sql
SQL> alter session set sql_trace=true;

Session altered

SQL> execute dbms_refresh.refresh('group1');

PL/SQL procedure successfully completed

SQL> alter session set sql_trace=false;

Session altered

==格式化trace文件
C:oracleoracle920adminyuzhudump>tkprof yuzh_ora_1208.trc
output = refresh_group_com.out

TKPROF: Release 9.2.0.1.0 - Production on 星期五 1月 21 17:20:03 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

==格式化后的trace文件摘要
...
delete from "SCOTT"."EMP_MV"
...
INSERT INTO "SCOTT"."EMP_MV"("EMPNO","ENAME","SAL") SELECT "EMP"."EMPNO",
  "EMP"."ENAME","EMP"."SAL" FROM "EMP" "EMP"
...
delete from "SCOTT"."DEPT_MV"
...
INSERT INTO "SCOTT"."DEPT_MV"("DEPTNO","DNAME","LOC") SELECT "DEPT"."DEPTNO",
  "DEPT"."DNAME","DEPT"."LOC" FROM "DEPT" "DEPT"
  ...

==refresh失败测试
SQL> insert into dept values(60,'aaa','Shanghai');

1 row inserted

SQL> commit;

Commit complete

SQL> alter table dept drop column loc;

Table altered


SQL> alter session set sql_trace=true;

Session altered

SQL> exec dbms_refresh.refresh('group1');

begin dbms_refresh.refresh('group1'); end;

ORA-12008: êμì??ˉêóí?μ??¢D??·???D′??ú′í?ó
ORA-00904: "DEPT"."LOC": ?TD§μ?±êê?·?
ORA-06512: ?ú"SYS.DBMS_SNAPSHOT", line 794
ORA-06512: ?ú"SYS.DBMS_SNAPSHOT", line 851
ORA-06512: ?ú"SYS.DBMS_IREFRESH", line 683
ORA-06512: ?ú"SYS.DBMS_REFRESH", line 195
ORA-06512: ?úline 1

SQL> alter session set sql_trace=false;

Session altered

SQL> select * from dept_mv;

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    50 programmer     Hangzhou
 
--纪录都还在,表明自动回滚了

 

 

作为对照,做单个materialized view的完全刷新测试

==创建1个MV
SQL>      create materialized view dept_mv2
  2       build immediate
  3       refresh complete
  4       on demand
  5       enable query rewrite
  6       as
  7       select * from dept;

Materialized view created

====执行complete refresh,并跟踪sql
SQL> alter session set sql_trace=true;

Session altered

SQL> exec dbms_mview.refresh('dept_mv2','c',null,true,false,1,0,0,true);

PL/SQL procedure successfully completed

SQL> alter session set sql_trace=false;

Session altered


==格式化trace文件
C:oracleoracle920adminyuzhudump>tkprof yuzh_ora_1556.trc output=refresh_mv_com.out

TKPROF: Release 9.2.0.1.0 - Production on 星期五 1月 21 17:36:55 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

==格式化后的trace文件摘要
...
truncate table "SCOTT"."DEPT_MV2" purge snapshot log
...
INSERT /*+ APPEND */ INTO "SCOTT"."DEPT_MV2"("DEPTNO","DNAME","LOC") SELECT
  "DEPT"."DEPTNO","DEPT"."DNAME","DEPT"."LOC" FROM "DEPT" "DEPT"
...
==refresh失败测试

SQL>  alter session set sql_trace=true;

Session altered

SQL> exec dbms_mview.refresh('dept_mv2','c',null,true,false,1,0,0,true);

begin dbms_mview.refresh('dept_mv2','c',null,true,false,1,0,0,true); end;

ORA-12008: êμì??ˉêóí?μ??¢D??·???D′??ú′í?ó
ORA-00904: "DEPT"."LOC": ?TD§μ?±êê?·?
ORA-00904: "DEPT"."LOC": ?TD§μ?±êê?·?
ORA-06512: ?ú"SYS.DBMS_SNAPSHOT", line 794
ORA-06512: ?ú"SYS.DBMS_SNAPSHOT", line 851
ORA-06512: ?ú"SYS.DBMS_SNAPSHOT", line 832
ORA-06512: ?úline 1

SQL> alter session set sql_trace=false;

Session altered

SQL> select * from dept_mv2;

DEPTNO DNAME          LOC
------ -------------- -------------

--纪录为0

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-787903/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2008796