Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view does not refresh when remote connection is dropped temporarily (Oracle 9, Windows Server 2003)
Materialized view does not refresh when remote connection is dropped temporarily [message #315474] Mon, 21 April 2008 10:53 Go to next message
blakhama
Messages: 10
Registered: August 2006
Junior Member
The issue is that a scheduled refresh does not occur when the remote connection to our as400/db2 link drops out around the scheduled time the view tries to refreh. It seems the NEXT refresh does not update (even when the remote connection is working) and is stuck on the old refresh time. Is there any work arounds, or ways to update the NEXT date? The MV works fine up until the remote connection drops out (which may be a few times a week). Here is the code:

CREATE MATERIALIZED VIEW DEV.MV_ERR
CACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE(SYSDATE,'dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE + .05/24
WITH PRIMARY KEY
AS
SELECT "A", "B", "C", "D",
FROM library.table@as400_db2;

Thanks

[Updated on: Mon, 21 April 2008 10:57]

Report message to a moderator

Re: Materialized view does not refresh when remote connection is dropped temporarily [message #315481 is a reply to message #315474] Mon, 21 April 2008 12:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Have you tried setting QUERY_REWRITE_INTEGRITY = stale_tolerated? That might keep it from being broken and allow it to continue. What does dbms_mview.explain_mview show?
Re: Materialized view does not refresh when remote connection is dropped temporarily [message #315497 is a reply to message #315474] Mon, 21 April 2008 13:26 Go to previous messageGo to next message
blakhama
Messages: 10
Registered: August 2006
Junior Member
Sorry I'm a .Net, MS DNA developer, not a DBA so bare with me if I am mistating or not understanding.

I'm not sure how I would use QUERY_REWRITE_INTEGRITY?

However, I was thinking of the alternative (which is not working at the moment, combining the jobs):
BEGIN
SYS.DBMS_JOB.REMOVE(76);
COMMIT;
END;
/

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'dbms_refresh.refresh(''"DSS"."MV_ERR1"'');dbms_refresh.refresh(''"DSS"."MV_ERR2"'');dbms_refresh.refresh(''"DSS"."MV_ERR3 "'');dbms_refresh.refresh(''"DSS"."MV_ERR4"'');'
,next_date => to_date('21/04/2008 14:15:32','dd/mm/yyyy hh24:mi:ss')
,interval => 'SYSDATE + .05/24 '
,no_parse => FALSE
,refresh_after_errors => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

But the issue is, I belive my syntax is incorrect for combining or grouping the MV's refreshes:
,what => 'dbms_refresh.refresh(''"DSS"."MV_ERR1"'');dbms_refresh.refresh(''"DSS"."MV_ERR2"'');dbms_refresh.refresh(''"DSS"."MV_ERR3 "'');dbms_refresh.refresh(''"DSS"."MV_ERR4"'');'


I believe the following seems like it will address the issue:
refresh_after_errors => TRUE
Re: Materialized view does not refresh when remote connection is dropped temporarily [message #315498 is a reply to message #315497] Mon, 21 April 2008 13:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
To set the query_rewrite_integrity, you would alter system or alter session:

SCOTT@orcl_11g> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=STALE_TOLERATED;

Session altered.

SCOTT@orcl_11g>

You have given conflicting examples. Are you setting the refresh interval in the view or through dbms_job or both or switching from one to the other or what? I was under the impression after your first post that everything was working correctly as long as the connection is not lost. Is that the case or do you still need to get it to refresh properly when the connection is not lost?

If you are going to use dbms_refresh, then you use the make procedure to make a refresh group and the refresh procedure to refresh the whole group. The syntax is in the documentation here:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28327/rarrefreshpac.htm#i94057

Setting refresh_after_errors in the dmbs_refresh.make procedure sounds like an idea worth trying.

Re: Materialized view does not refresh when remote connection is dropped temporarily [message #315512 is a reply to message #315474] Mon, 21 April 2008 15:36 Go to previous messageGo to next message
blakhama
Messages: 10
Registered: August 2006
Junior Member
OK, let's stick with my original post "I was under the impression after your first post that everything was working correctly as long as the connection is not lost."

Sorry, I was confusing myself.

I wound up creating a procedure that updates the jobs that refresh the MV's (from the remote db2 tabels). So if the refresh for the MV is out of sync, I have another job that calls a local procedure which runs every 12 minutes. The procedure is not dependent on the remote db2 connection. OK, here is the working non DBA solution:

CREATE OR REPLACE PROCEDURE DSS.MV_ERR_REFRESH IS
BEGIN
BEGIN
SYS.DBMS_JOB.CHANGE
(
job => 88
,what => 'dbms_refresh.refresh(''"DSS"."MV_ERR1"'');'
,next_date => SYSDATE+.05/24
,interval => 'SYSDATE+.05/24 '
);
END;
BEGIN
SYS.DBMS_JOB.CHANGE
(
job => 89
,what => 'dbms_refresh.refresh(''"DSS"."MV_ERR2"'');'
,next_date => SYSDATE+.05/24
,interval => 'SYSDATE+.05/24 '
);
END;
BEGIN
SYS.DBMS_JOB.CHANGE
(
job => 90
,what => 'dbms_refresh.refresh(''"DSS"."MV_ERR3"'');'
,next_date => SYSDATE+.05/24
,interval => 'SYSDATE+.05/24 '
);
END;
BEGIN
SYS.DBMS_JOB.CHANGE
(
job => 91
,what => 'dbms_refresh.refresh(''"DSS"."MV_ERR4"'');'
,next_date => SYSDATE+.05/24
,interval => 'SYSDATE+.05/24 '
);
END;
END MV_ERR_REFRESH;
/

This may not be the best solution, but it works so far. WHat do you think?

Thanks for the help!
Re: Materialized view does not refresh when remote connection is dropped temporarily [message #316376 is a reply to message #315512] Thu, 24 April 2008 18:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
If it works and you are happy with it, then that's fine. I would be curious as to why the view alone did not work and would be poking around and looking at user_mviews and dbms_mview.explain_mview. I was unable to duplicate your problem by dropping then recreating a loopback database link. The view resumed refreshing once the link was re-established.
Re: Materialized view does not refresh when remote connection is dropped temporarily [message #316382 is a reply to message #316376] Thu, 24 April 2008 20:54 Go to previous message
blakhama
Messages: 10
Registered: August 2006
Junior Member
I believe the problem might be due to using the oracle transparent gateway to connect to as400/db2. I will look into the user_mviews and dbms_mview.explain_mview tomorrow and post the info here.

thanks
Previous Topic: ORA-00904: : invalid identifier
Next Topic: Putting multiple processes on rownum selection
Goto Forum:
  


Current Time: Wed Dec 07 04:29:59 CST 2016

Total time taken to generate the page: 0.14151 seconds