MV Refresh Not Happen Automatically [message #348284] |
Tue, 16 September 2008 05:55  |
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   |
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;
|
|
|
|