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

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

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

这篇文章讨论对于本地物化视图，如何定位刷新组中刷新失败的物化视图。

先构造一个简单的例子：

SQL> SELECT * FROM V$VERSION;

BANNER
*k#sa j@ Dy)v0----------------------------------------------------------------ITPUB个人空间&_6z5s:N`0kl
Oracle9iEnterpriseEdition Release9.2.0.4.0 -Production
] [P&osN0P0PL/SQL Release 9.2.0.4.0 - Production
.x#b%kYH E|0CORE    9.2.0.3.0       Production
^4H[B0U t+N!G:w0TNS for Linux: Version 9.2.0.4.0 - Production
&oaD,{I$W*Z$B:m0NLSRTL Version 9.2.0.4.0 - Production

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> 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> CREATE MATERIALIZED VIEW MV_T1 REFRESH FAST AS SELECT * FROM T1;

实体化视图已创建。

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

实体化视图已创建。

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

实体化视图已创建。

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

PL/SQL过程已成功完成。

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

已创建1行。

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

已创建1行。

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

已创建1行。

SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')

PL/SQL过程已成功完成。

如果对表T2进行了修改：

SQL> ALTER TABLE T2 MODIFY NAME VARCHAR2(32);

表已更改。

SQL> INSERT INTO T1 VALUES (101, 'B');

已创建1行。

SQL> INSERT INTO T2 VALUES (101, LPAD('B', 32, 'B'));

已创建1行。

SQL> INSERT INTO T3 VALUES (101, 'B');

已创建1行。

SQL> COMMIT;

提交完成。

SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROM USER_MVIEWS;

MVIEW_NAME                     LAST_REFRESH_DATE   STALENESSITPUB个人空间\{&t N#u&`1u8uP#[
------------------------------ ------------------- -------------------ITPUB个人空间2TpYZP)?Z:a$}
MV_T1                          2008-01-23 19:22:43 NEEDS_COMPILE
sMU l&k`_TH X0MV_T2                          2008-01-23 19:22:43 NEEDS_COMPILEITPUB个人空间9H-I[&T!pv!fc~8\Od
MV_T3                          2008-01-23 19:22:43 NEEDS_COMPILE

SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')ITPUB个人空间7fF UGjj?u
BEGIN DBMS_REFRESH.REFRESH('REP_TEST'); END;

*ITPUB个人空间6Qs&Y\2_}
第1行出现错误:
P0l/u#prcn)l0ORA-12008:实体化视图的刷新路径中存在错误ITPUB个人空间&Jj.z[4w
ORA-01401:插入的值对于列过大ITPUB个人空间2]bQ8P/o
ORA-06512:在"SYS.DBMS_SNAPSHOT", line 794
"UM-B%YF&A9NS,A0ORA-06512:在"SYS.DBMS_SNAPSHOT", line 851ITPUB个人空间2vL~#]D;Nz(AY
ORA-06512:在"SYS.DBMS_IREFRESH", line 683ITPUB个人空间F4P b
?8_.RN3w
ORA-06512:在"SYS.DBMS_REFRESH", line 195
A cQRU]-r0ORA-06512:在line 1

这时就会出现刷新错误，而Oracle给出的错误信息虽然很明确，但是并没有指出具体是那张表出现了错误，而只有找到错误的表才能定位并解决问题。

其实这个问题的解决方法有很多，比如写一个过程，对刷新组中所有的物化视图进行依次刷新，出现错误时弹出异常。也可以利用SQL_TRACE、系统级错误触发器等手段。

不过最方便的方法莫过于查询USER_MVIEWS视图：

SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROM USER_MVIEWS;

MVIEW_NAME                     LAST_REFRESH_DATE   STALENESSITPUB个人空间o3L0P2zB\
~8p G
------------------------------ ------------------- -------------------
QQ \-W:UA0MV_T1                          2008-01-23 19:24:56 FRESH
(^_'f,n.C0MV_T2                          2008-01-23 19:22:43 STALEITPUB个人空间!q.Rp.zmB\A
MV_T3                          2008-01-23 19:22:43 NEEDS_COMPILE

对于刷新成功的物化视图，LAST_REFRESH_DATE会比刷新组进行刷新的时间大。而对于还没有刷新的物化视图，STALENESS的状态应该是NEEDS_COMPILE或者FRESH。而对于基表发生了变化导致物化视图刷新失败，则会导致物化视图的STALENESS变为STALE。通过这种方法就可以简单的定位刷新出现错误的物化视图。

当然上面的办法是对9i而言，而在10g中，Oracle返回的错误信息更加详细，可以直接定位到错误的表和原因：

SQL> SELECT * FROM V$VERSION;

BANNER
o7]'q/Oq2@2QUH0----------------------------------------------------------------
;un@x~,_o$D4a0Oracle Database10gEnterpriseEdition Release10.2.0.3.0 - 64biITPUB个人空间xY0H}L Z%[Tj
PL/SQL Release 10.2.0.3.0 - ProductionITPUB个人空间I{)r$[d2c
CORE    10.2.0.3.0      Production
8mF5d2r9B,cO0{+j&E0TNS for Linux: Version 10.2.0.3.0 - Production
0eZ+b5D"VLq0NLSRTL Version 10.2.0.3.0 - Production

SQL> 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> CREATE MATERIALIZED VIEW MV_T1 REFRESH FAST AS SELECT * FROM T1;

实体化视图已创建。

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

实体化视图已创建。

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

实体化视图已创建。

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

PL/SQL过程已成功完成。

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

已创建1行。

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

已创建1行。

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

已创建1行。

SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')

PL/SQL过程已成功完成。

SQL> ALTER TABLE T2 MODIFY NAME VARCHAR2(35);

表已更改。

SQL> INSERT INTO T2 VALUES (101, LPAD('A', 35, 'A'));

已创建1行。

SQL> COMMIT;

提交完成。

SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')
$|wl(K C0BEGIN DBMS_REFRESH.REFRESH('REP_TEST'); END;

*
~5_s(c,c4Ur
r0第1行出现错误:
|)]&sSNR$P0ORA-12048:刷新实体化视图"TEST"."MV_T2"时出错ITPUB个人空间O`9vYB z.m
ORA-12899:列"TEST"."MV_T2"."NAME"的值太大(实际值: 35,最大值: 30)
sJqT)y*Z0ORA-06512:在"SYS.DBMS_SNAPSHOT", line 2254
l2Tx"h8E\_0ORA-06512:在"SYS.DBMS_SNAPSHOT", line 2460
Xb{b%}0ORA-06512:在"SYS.DBMS_IREFRESH", line 683
P*s1r6S)w*Vi0ORA-06512:在"SYS.DBMS_REFRESH", line 195ITPUB个人空间Kd F+L:ET0CH
ORA-06512:在line 1