物化檢視(Materialized View)的重新整理回滾測試

物化檢視在完全重新整理(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