物化视图在完全刷新(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