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

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

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

先构造一个简单的例子：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL 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 STALENESS
------------------------------ ------------------- -------------------
MV_T1 2008-01-23 19:22:43 NEEDS_COMPILE
MV_T2 2008-01-23 19:22:43 NEEDS_COMPILE
MV_T3 2008-01-23 19:22:43 NEEDS_COMPILE

SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')
BEGIN DBMS_REFRESH.REFRESH('REP_TEST'); END;

*第 1 行出现错误:
ORA-12008: 实体化视图的刷新路径中存在错误
ORA-01401: 插入的值对于列过大
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

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

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

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

SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROM USER_MVIEWS;

MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- -------------------
MV_T1 2008-01-23 19:24:56 FRESH
MV_T2 2008-01-23 19:22:43 STALE
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
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL 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')
BEGIN DBMS_REFRESH.REFRESH('REP_TEST'); END;

*第 1 行出现错误:
ORA-12048: 刷新实体化视图 "TEST"."MV_T2" 时出错
ORA-12899: 列 "TEST"."MV_T2"."NAME" 的值太大 (实际值: 35, 最大值: 30)
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: 在 "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: 在 "SYS.DBMS_IREFRESH", line 683
ORA-06512: 在 "SYS.DBMS_REFRESH", line 195
ORA-06512: 在 line 1

我照着你的步骤做,怎么显示可以刷新成功啊,其实没有刷新,又试一遍还是一样
帮我看看怎么回事?版本:9.2.0.1.0
OS:winxp

SQL> SELECT * FROM T2 WHERE ID=10001;

ID NAME
---------- --------------------------------
10001 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

SQL> DESC MV_T2;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)

SQL>
SQL> alter table t2 modify name varchar2(40);

表已更改。

SQL> insert into t1 values(54321,'x');

已创建 1 行。

SQL> insert into t2 values(54321,LPAD('x',40,'x');
insert into t2 values(54321,LPAD('x',40,'x')
*
第 1 行出现错误:
ORA-00917: 缺少逗号

SQL> insert into t2 values(54321,LPAD('x',40,'x'));

已创建 1 行。

SQL> insert into t3 values(54321,'x');

已创建 1 行。

SQL> select mview_name,last_refresh_date,staleness from user_mv

MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- ------------
MV_T1 2008-01-24 16:02:31 FRESH
MV_T2 2008-01-24 16:02:31 NEEDS_COMPIL
MV_T3 2008-01-24 16:02:31 FRESH

SQL> desc t2;
名称 是否为空? 类型
----------------------------------------- -------- -----------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(40

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

PL/SQL 过程已成功完成。

SQL> select mview_name,last_refresh_date,staleness from user_mv

MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- ------------
MV_T1 2008-01-24 16:05:25 FRESH
MV_T2 2008-01-24 16:05:25 FRESH
MV_T3 2008-01-24 16:05:25 FRESH

SQL> select * from mv_t2 where id=54321;

未选定行

SQL> select * from mv_t1 where id=54321;

未选定行

SQL> commit
2 ;

提交完成。

SQL> select mview_name,last_refresh_date,staleness from user_mv

MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- ------------
MV_T1 2008-01-24 16:05:25 FRESH
MV_T2 2008-01-24 16:05:25 FRESH
MV_T3 2008-01-24 16:05:25 FRESH

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

PL/SQL 过程已成功完成。

SQL> select * from mv_t2 where id=54321;

未选定行

SQL>

把前面的步骤也贴一下

下面是我又执行一遍得代码,从中可以看出我得问题是:运行exec dbms_refresh.refresh('REP_TEST')语句之后显示成功,可是实际上物化视图组并没有刷新.
同时,我在本机得10G上重复上述过程,没有这个问题.
我想可能得原因是:
1.我得9i是连接得远端的服务器,没法刷新?好像不可能
2.我装得软件没有这个包,可是提示PL/SQL过程成功了?

小弟刚学oracle,老大帮忙看看,多谢.

Cocuments and SettingsAdministrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 1月 25 09:53:10 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn orcl/orcl@122 as sysdba
已连接。
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.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');
BEGIN dbms_refresh.make('REP_TEST','MV_T1,MV_T2,MV_T3',SYSDATE,'SYSDATE + 1'); END;

*
第 1 行出现错误:
ORA-23403: 刷新组"SYS"."REP_TEST" 已存在
ORA-06512: 在"SYS.DBMS_SYS_ERROR", line 95
ORA-06512: 在"SYS.DBMS_IREFRESH", line 210
ORA-06512: 在"SYS.DBMS_IREFRESH", line 365
ORA-06512: 在"SYS.DBMS_REFRESH", line 88
ORA-06512: 在"SYS.DBMS_REFRESH", line 62
ORA-06512: 在line 1

SQL> exec dbms_refresh.destroy(name=>'REP_TEST');

PL/SQL 过程已成功完成。

SQL> exec dbms_refresh.make('REP_TEST','MV_T1,MV_T2,MV_T3',SYSDATE,'SYSDATE + 1');

PL/SQL 过程已成功完成。

SQL> insert into t1 values (10000,'a');

已创建 1 行。

SQL> insert into t2 values (10000,'a');

已创建 1 行。

SQL> insert into t3 values (10000,'a');

已创建 1 行。

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

PL/SQL 过程已成功完成。

SQL> select * from mv_t2 where id=10000;

未选定行

SQL> select * from t2 where id=10000;

ID NAME
---------- ------------------------------
10000 a

SQL> alter table t2 modify name varchar2(35);

表已更改。

SQL> insert into t1 values(10001,'b');

已创建 1 行。

SQL> insert into t2 values(10001,LPAD('b',35,'b'));

已创建 1 行。

SQL> insert into t3 values(10001,'b');

已创建 1 行。

SQL> select mview_name,last_refresh_date,staleness from user_mviews;

MVIEW_NAME LAST_REFRESH_DATE STALENESS
------------------------------ ------------------- -------------------
MV_T1 2008-01-25 10:24:59 NEEDS_COMPILE
MV_T2 2008-01-25 10:24:59 NEEDS_COMPILE
MV_T3 2008-01-25 10:24:59 NEEDS_COMPILE

SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST');

PL/SQL 过程已成功完成。

似乎是物化视图没有刷新。

直接执行DBMS_MVIEW.REFRESH('MV_T1')
然后检查物化视图是否存在数据

SQL> EXEC dbms_mview.refresh('MV_T1');

PL/SQL 过程已成功完成。

SQL> SELECT * FROM MV_T1 WHERE ID=10005;

未选定行

还是提示成功可实际上没有刷新

SQL> select * from t2 where id=10005;

ID NAME
---------- -----------------------------------
10005 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

这条记录在表中是有的

SQL> SELECT MVIEW_NAME FROM USER_MVIEWS;

MVIEW_NAME
------------------------------
MV_T1
MV_T2
MV_T3

这个物化视图也存在,可为什么不刷新呢?

查询一下mlog$_t1，看看是否有记录。
然后插入一条记录到t1，看看mlog$_t1是否有记录。

SQL> select * from mlog$_t1;

未选定行

SQL> insert into t1 values(10006,'a');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from mlog$_t1;

未选定行

SQL> select * from mlog$_T2;

未选定行

SQL> insert into t2 values (10009,LPAD('A',35,'A'));

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM MLOG$_T2;

未选定行

SQL> select mview_name,last_refresh_date,staleness f

MVIEW_NAME LAST_REFRESH_D STALEN
------------------------------ -------------- ------
MV_T1 28-1月 -08 NEEDS_
MV_T2 28-1月 -08 NEEDS_
MV_T3 28-1月 -08 FRESH

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

PL/SQL 过程已成功完成。

SQL> select mview_name,last_refresh_date,staleness f

MVIEW_NAME LAST_REFRESH_D STALEN
------------------------------ -------------- ------
MV_T1 28-1月 -08 FRESH
MV_T2 28-1月 -08 FRESH
MV_T3 28-1月 -08 FRESH

SQL> select * from mlog$_t1;

未选定行

SQL> commit;

提交完成。

SQL> select * from mlog$_t1;

mlog$_t1没有记录.其他mlog$_t2也没有记录

看来是物化视图日志的问题。

去论坛发个帖子吧，这里估计很难描述清楚了。

谢谢了