请教物化视图问题，

儿子属羊

最初由 ekunge 发布
[B]SQL> exec dbms_mview.refresh('akunge1','f');

begin dbms_mview.refresh('akunge1','f'); end;

ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_DEFER_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

不知道是不是我手动刷新的语句写法不对还是什么，
好像手动也不能刷新
去找了4068和4063的提示，不过看得不是十分明白 [/B]

刷新的用户不对！

ekunge

最初由 fusnow 发布
[B]用
exec dbms_mview.refresh('akunge1','C');
报错么？ [/B]

SQL> exec dbms_mview.refresh('akunge1','C');

begin dbms_mview.refresh('akunge1','C'); end;

ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_DEFER_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

报错是一样的

ekunge

最初由 儿子属羊 发布
[B]

刷新的用户不对！ [/B]

可是我的物化视图akunge1就是在这个用户下建立的，
ekunge1也是在这个用户下，
怎么会用户不对呢，
是不是有什么系统权限的问题

ChocolateTwo

这个问题解决没呀。。。我也遇到这个问题。请大家帮看看。。。。。系统环境：

1. System name:    HP-UX
   
2. Node name:      hpsvr1
   
3. Release:        B.11.23
   
4. Version:        U
   
5. Machine:        ia64
   
6. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

复制代码

为测试是不是bug问题，测试时表及物化视图创建语句：
-------------------------------------------测试代码--------------------------------------------------------------------

1. create table tm
   
2. (
   
3.   PK                      NUMBER(19) not null,
   
4.   CREATETIME              DATE,
   
5.   LASTMODIFYTIME          DATE
   
6. );
   
7. 
   
8. create  materialized view mv_test
   
9. refresh force on demand
   
10. as
    
11. select  tm.pk, tm.createtime,
    
12.       tm.lastmodifytime
    
13. from  tm;

复制代码

  执行刷新：   

1. begin
   
2.         dbms_mview.refresh('mv_test');
   
3. end;

复制代码

报错：

1. ORA-04063: package body "SYS.DBMS_SNAPSHOT" has errors
   
2. ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SNAPSHOT"
   
3. ORA-06512: at line 2
   

复制代码

---------------------------------------------------------测试部分结束--------------------------------------------------------------------------------------------

PS:表空间未满，磁盘空间也有。

不知道这信息是否跟物化视图刷新有关，也不知道materialized view刷新是不是库的bug。。。

ChocolateTwo

在此贴一下实际库的告警日志及trc文件信息：
$ tail -f alert_center.log

1. $ tail -f alert_center.log
   
2. ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SNAPSHOT"
   
3. ORA-06512: at "CENTER.SP_CHECK_VWCUST", line 16
   
4. ORA-06512: at line 1
   
5. has errors
   
6. Wed Jul 25 05:05:44 2012
   
7. Memory Notification: Library Cache Object loaded into SGA
   
8. Heap size 4117K exceeds notification threshold (2048K)
   
9. Wed Jul 25 05:06:32 2012
   
10. ORA-24010 encountered when generating server alert SMG-4121
    
11. ORA-24010 encountered when generating server alert SMG-4121
    
12. Wed Jul 25 05:10:00 2012
    
13. Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/center/bdump/center_j000_26689.trc:
    
14. ORA-12012: error on auto execute of job 57611
    
15. ORA-04063: ORA-04063: package body "SYS.DBMS_SNAPSHOT" has errors
    
16. ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SNAPSHOT"
    
17. ORA-06512: at "CENTER.SP_CHECK_VWPROD", line 16
    
18. ORA-06512: at line 1
    
19. has errors
    
20. Wed Jul 25 05:11:32 2012
    
21. ORA-24010 encountered when generating server alert SMG-4120
    
22. ORA-24010 encountered when generating server alert SMG-4121
    
23. ORA-24010 encountered when generating server alert SMG-4121

复制代码

SP_CHECK_VWPROD是刷新物化视图vw_prod的存储过程，REFRESH_VW_PROD是对应的JOB

1. create or replace procedure sp_check_vwprod
   
2. is
   
3. v_date date;
   
4. p_date date;
   
5. begin
   
6.   select max(lastmodifytime) into v_date from (
   
7.   select max(lastmodifytime) lastmodifytime from t1
   
8.   union
   
9.   select max(lastmodifytime) from t2
   
10.   union
    
11.   select max(lastmodifytime) from t3
    
12.   union
    
13.   select max(lastmodifytime) from t4);
    
14.   select pdate into p_date from tmp_prod_params;
    
15.   if v_date-p_date >0 then
    
16.      dbms_mview.refresh('vw_prod');
    
17.     update tmp_prod_params set pdate=v_date;
    
18.   end if;
    
19.   commit;
    
20. end;
    

复制代码

表tmp_prod_params  只有一个pdate (date 类型)字段，记录刷新的时间点。

ChocolateTwo

我的问题解决了。我首先根据报错，进行了一个编译无效对象的操作，发现DBMS_SNAPSHOT这个报错，跟其相关的一些DEFTRANDEST 、_DEFTRANDEST 视图有错误，查看了一下select * from dba_objects where object_name in('DBMS_SNAPSHOT','_DEFTRANDEST') 发现status = invalid.
于是重建字典（执行catalog.sql）再编译一下就好了。
可以刷新物化视图了。