Materialized view is not getting refreshed

From: KSS Vijaya Bhaskar <kssvbhaskar_at_indiatimes.com>
Date: 31 May 2004 04:00:49 -0700
Message-ID: <bde31ae9.0405310300.432ca78d@posting.google.com>

Hi All,

We have created a materialized view as follows (in Oracle 8.1.7). [Please bear with me for pasting the commands and observations in verbatim, doing only to give you more information for the problem in hand]

"create materialized view INFO_EXCEPTIONS

   tablespace WEB02
   storage (initial 100k next 100k pctincrease 0)    refresh complete
   start with SYSDATE next TRUNC(SYSDATE)+1    with ROWID
   as select class,engineer,identifier,module,project,

        resolved_on,severity,showstopper,status,submitted_on,version,when_found
        from DEFECTS_at_ORADDTS
        where engineer in (select ddts_id from INFO_ENG) and 
          (resolved_on-submitted_on)/(DeCode(Severity,1,1,2,2,3,Decode(ShowStopper,'Y',

15,80),80)) > 1"

The view is getting created and is being materialized the first time. We are expecting the view to get refreshed every day at 12:00am. But, to our observation, the view is not getting refreshed.

Also, to dig further, we queried USER_MVIEWS for this view and found that the following.

OWNER				WEB
MVIEW_NAME			INFO_EXCEPTIONS
CONTAINER_NAME		INFO_EXCEPTIONS
QUERY				<Long>
QUERY_LEN				291
UPDATABLE				N
UPDATE_LOG	
MASTER_ROLLBACK_SEG	
MASTER_LINK			@ORADDTS
REWRITE_ENABLED		N
REWRITE_CAPABILITY		NONE
REFRESH_MODE			DEMAND
REFRESH_METHOD		COMPLETE
BUILD_MODE			IMMEDIATE
FAST_REFRESHABLE		NO
LAST_REFRESH_TYPE		COMPLETE
LAST_REFRESH_DATE		27-May-04
STALENESS				UNDEFINED
AFTER_FAST_REFRESH		UNDEFINED
COMPILE_STATE			ERROR

Tried to a good extent searching the web and usenet for any discussions on these. But, couldn't find much. One thing I could find was a two message discussion at the following URL [http://www.orafaq.com/cgi-bin/msgboard/view-thr.cgi?board=replication&message=877].  This is about the status updation of USER_MVIEWS table.

But, we could observe that the view is not getting refreshed as expected every day at 12am. Any suggestions/insights into this.

Thanks,
Vijay Received on Mon May 31 2004 - 06:00:49 CDT