如何确定导致刷新组刷新失败的物化视图（二）

当系统中存在多个物化视图，使用刷新组会大大简化物化视图的刷新，只需要将同一时间同步的物化视图全都放到一个刷新组中既可。

在9i中，使用刷新组方便的时候有一个缺点，如果其中一个物化视图报错，Oracle给出的错误信息并没有说明是哪个物化视图出了问题。

这篇文章描述一种对于各种情况均适用的方法。

如何确定导致刷新组刷新失败的物化视图（一）：http://yangtingkun.itpub.net/post/468/452773

上一篇介绍了本地物化视图如何定位刷新失败的物化视图，方法很简单，可惜不适用于通过数据库链建立的物化视图。

看下面的例子：

SQL> CONN YANGTK/YANGTK@YTK102ITPUB个人空间1hu:[ G2bAS8c
已连接。
#KMw"UZ(R+ye?0SQL> CREATE TABLE T1 (ID PRIMARY KEY, NAME) AS SELECT ROWNUM, TNAME FROM TAB;

表已创建。

SQL> CREATE TABLE T2 (ID PRIMARY KEY, NAME) AS SELECT ROWNUM, TNAME FROM TAB;

表已创建。

SQL> CREATE TABLE T3 (ID PRIMARY KEY, NAME) AS SELECT ROWNUM, TNAME FROM TAB;

表已创建。

SQL> CREATE MATERIALIZED VIEW LOG ON T1;

实体化视图日志已创建。

SQL> CREATE MATERIALIZED VIEW LOG ON T2;

实体化视图日志已创建。

SQL> CREATE MATERIALIZED VIEW LOG ON T3;

实体化视图日志已创建。

SQL> CONN YANGTK/YANGTK@YTK92ITPUB个人空间)a)Rq"_#{2ZR!OU8QZ
已连接。
L6s K/BCd$C0SQL> CREATE DATABASE LINK YTK102 USING 'YTK102';

数据库链接已创建。

SQL> CREATE MATERIALIZED VIEW MV_T1 REFRESH FAST AS SELECT * FROM T1@YTK102;

实体化视图已创建。

SQL> CREATE MATERIALIZED VIEW MV_T2 REFRESH FAST AS SELECT * FROM T2@YTK102;

实体化视图已创建。

SQL> CREATE MATERIALIZED VIEW MV_T3 REFRESH FAST AS SELECT * FROM T3@YTK102;

实体化视图已创建。

SQL> EXEC DBMS_REFRESH.MAKE('REP_TEST', 'MV_T1,MV_T2,MV_T3', SYSDATE + 1, 'SYSDATE + 1')

PL/SQL过程已成功完成。

SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')

PL/SQL过程已成功完成。

SQL> CONN YANGTK/YANGTK@YTK102
!b
{1ds6q(~
fc0已连接。ITPUB个人空间zv]bVeg
SQL> ALTER TABLE T2 MODIFY NAME VARCHAR2(32);

表已更改。

SQL> INSERT INTO T1 VALUES (100, 'A');

已创建1行。

SQL> INSERT INTO T2 VALUES (100, RPAD('A', 32, 'A'));

已创建1行。

SQL> INSERT INTO T3 VALUES (100, 'A');

已创建1行。

SQL> COMMIT;

提交完成。

SQL> CONN YANGTK/YANGTK@YTK92ITPUB个人空间)z,GZ(sBzSz
已连接。ITPUB个人空间iZ8?L|!v*vt,}
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROM USER_MVIEWS;

MVIEW_NAME                     LAST_REFRESH_DATE   STALENESS
sZ!O})M*o5K8@ Z4h.F#h0------------------------------ ------------------- -------------------ITPUB个人空间x am0}8?6c}'D9l0@
MV_T1                          2008-01-24 15:45:43 UNDEFINEDITPUB个人空间$cy6a#eA J7f
MV_T2                          2008-01-24 15:45:43 UNDEFINED
)cCwZ2c dY0MV_T3                          2008-01-24 15:45:43 UNDEFINED

SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')ITPUB个人空间-WWr.L.X6l ]2d
BEGIN DBMS_REFRESH.REFRESH('REP_TEST'); END;

*ITPUB个人空间-g8? {v%R ?'?
第1行出现错误:ITPUB个人空间&W@f n,h)D+t+F ]hc
ORA-12008:实体化视图的刷新路径中存在错误
b"U;T3v*blL [0ORA-01401:插入的值对于列过大
l,y*Z-T)Jmel0ORA-06512:在"SYS.DBMS_SNAPSHOT", line 794ITPUB个人空间Tkxr|Q$zkKD2yK/F
ORA-06512:在"SYS.DBMS_SNAPSHOT", line 851
7fUW'F!r0ORA-06512:在"SYS.DBMS_IREFRESH", line 683ITPUB个人空间GF;cSP
ORA-06512:在"SYS.DBMS_REFRESH", line 195
H)Lky0_)H0ORA-06512:在line 1

3eI0L;e[1].Pr0SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROM USER_MVIEWS;

MVIEW_NAME                     LAST_REFRESH_DATE   STALENESSITPUB个人空间Fj8z M,XS a}!Y!Iz}
------------------------------ ------------------- -------------------
A$R$n5ys
e1v%M0MV_T1                          2008-01-24 15:46:34 UNDEFINED
;f\~[Ln0MV_T2                          2008-01-24 15:45:43 UNDEFINEDITPUB个人空间)bF.{:t0Fl.O&D&g
MV_T3                          2008-01-24 15:45:43 UNDEFINED

完全仿照上一篇的方法，不过可惜的是，这种方法只能确定那些物化视图是没有问题的。刷新成功的物化视图，最后刷新时间会大于刷新组的最后刷新时间。但是无法确定没有刷新的物化视图中哪个出现了错误。

显然，这里需要另寻方法。考虑到Oracle的刷新机制，无非是从刷新组中依次读取物化视图，并对其进行刷新。那么检查USER_REFRESH_CHILDREN，检查是否存在表示顺序的列：

SQL> DESC USER_REFRESH_CHILDRENITPUB个人空间Lfy(y e4t9x
 名称                    是否为空?类型
;_N|Y#G~0 ------------------------ -------- ------------------
K+W&F4E1@[.q t0 OWNER                    NOT NULL VARCHAR2(30)
b"yN PM#`#qX0 NAME                     NOT NULL VARCHAR2(30)ITPUB个人空间S4z2E"W1pr\"zv+Y
 TYPE                              VARCHAR2(30)ITPUB个人空间6bJ-u&l*yA#s
 ROWNER                   NOT NULL VARCHAR2(30)ITPUB个人空间8])g@#~X+\)D
 RNAME                    NOT NULL VARCHAR2(30)
6V:iyL)y0 REFGROUP                          NUMBERITPUB个人空间8UL1iqR'B ^E
 IMPLICIT_DESTROY                  VARCHAR2(1)ITPUB个人空间-}PE/E%}
 PUSH_DEFERRED_RPC                 VARCHAR2(1)ITPUB个人空间nV4yH#R
 REFRESH_AFTER_ERRORS              VARCHAR2(1)
e`7v!jS'@0 ROLLBACK_SEG                      VARCHAR2(30)ITPUB个人空间2x;T
cr|h
 JOB                               NUMBERITPUB个人空间6~:o` }
Y
Jl
 NEXT_DATE                         DATEITPUB个人空间,srm%^(y3cu'LA
 INTERVAL                          VARCHAR2(200)
#lpDk#Nf0 BROKEN                            VARCHAR2(1)
#[WeU!f4KZ_0 PURGE_OPTION                      NUMBER(38)
iS-H\k!o#m|0 PARALLELISM                       NUMBER(38)
#r TSfM:r.j1c7Y0 HEAP_SIZE                         NUMBER(38)

视图中并没有包括物化视图的顺序，既然Oracle没有确定的读取顺序，那么就是根据全表扫描获取的顺序。也就是说，只要直接读取USER_REFRESH_CHILDREN视图就可以获得物化视图刷新的顺序。

SQL> SELECT NAME FROM USER_REFRESH_CHILDREN;

NAMEITPUB个人空间%x6W5f%p*g1}x5H
------------------------------ITPUB个人空间/VRsz \J
I&~g Gn
MV_T1ITPUB个人空间#k)Y+y#q| j/N5Y
MV_T2
(lwM/|h&{6E0MV_T3

但是如果刷新组中数据量很大，只看顺序是没有意义的，必须配合最后刷新时间一起检查，不过USER_REFRESH_CHILDREN和USER_MVIEWS都是视图，如果联合查询很容易产生视图的MERGE造成执行计划的改变，从而影响输出的顺序。为了确保查询结果根据USER_REFRESH_CHILDREN视图的顺序进行查询，这里选择了下面的方式：

SQL> SELECT NAME, (SELECT LAST_REFRESH_DATE FROM USER_MVIEWS WHERE MVIEW_NAME = NAME)
o&GS$JMKql]T0  2  FROM USER_REFRESH_CHILDREN;

NAME                           (SELECTLAST_REFRESHITPUB个人空间OK z5s!n$hl2V$k
------------------------------ -------------------ITPUB个人空间^3aT{dX
MV_T1                          2008-01-24 15:46:34ITPUB个人空间v8?x0g7E`)]_"?&g(vk B
MV_T2                          2008-01-24 15:45:43ITPUB个人空间a@VL KE-V'q4\
MV_T3                          2008-01-24 15:45:43

这样只要找到第一个小于刷新时间的物化视图就可以了。这种方法同样适用于本地物化视图。