Home » SQL & PL/SQL » SQL & PL/SQL » MV Refresh Not Happen Automatically (Oracle 10.2.0.3.0, Unix)
MV Refresh Not Happen Automatically [message #348284] Tue, 16 September 2008 05:55 Go to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
I've created 3 MV from a remote DB, all 3 are pointing to same schema and having same refresh method.
Remote DB doesn't have the logs, and as all the MV refresh type is of Force on Demand, it should refresh compltely.
2 of them are refreshing fine and 1 of them is not refreshing automatically.
I didn't find any entry in alert log saying refresh failed etc.

Below is the script of MV.
 CREATE MATERIALIZED VIEW "CO"."ACTNOTICE003"
  BUILD IMMEDIATE
  REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE+ 2/(60*24)
  WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
  DISABLE QUERY REWRITE
  AS SELECT * FROM "AMIT"."ACTNOTICE" "ACTNOTICE";



Below is the output of the SQL

  SQL> select MVIEW_NAME, LAST_REFRESH_TYPE, TO_char(LAST_REFRESH_DATE,'DD/MM/YYYY HH24:MI:SS') LRD, 
  AFTER_FAST_REFRESH from all_mviews where mview_name like 'ACT%';

  MVIEW_NAME                     LAST_REF LRD                 AFTER_FAST_REFRESH
------------------------------ -------- ------------------- -------------------
ACTNOTICE001                   COMPLETE 16/09/2008 16:09:39 FRESH
ACTNOTICE002                   COMPLETE 11/09/2008 19:22:02 NA   ACTNOTICE003                   COMPLETE 16/09/2008 16:14:59 FRESH


ACTNOTICE002 is having the problem, I've recomiled and rebuild that one also.


Kindly help me to understand the reason why it is not refreshing as others.

Thanks
Amit
Re: MV Refresh Not Happen Automatically [message #348289 is a reply to message #348284] Tue, 16 September 2008 06:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, the most likely option is that you didn't add a NEXT clause to the one that isn't refreshing.

To check that, could you post the results of this query:
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','ACTNOTICE002') from dual;
Re: MV Refresh Not Happen Automatically [message #348291 is a reply to message #348289] Tue, 16 September 2008 06:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or have a look at the *_SNAPSHOTS views - they have the next refresh time in them.
Previous Topic: USING OBJECT TYPE WITH BULK COLLECT
Next Topic: Need Urgent Help (error in oracle triger (bad binding variable))
Goto Forum:
  


Current Time: Tue Dec 06 11:53:11 CST 2016

Total time taken to generate the page: 0.09076 seconds