| Schedular not working [message #306288] |
Thu, 13 March 2008 10:00  |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
Hi Everybody,
I have created a materialized view which i need to schedule every day at 7 PM.
But the script is not somehow working.
Below is the script.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'IVRDB_REFRESH_test1',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'BEGIN mv_refresh_job(''EXEC DBMS_MVIEW.REFRESH(''ASDWLBDB.TEST_TAB'', ''C'')'');
END;',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'freq=daily; byhour=19; byminute=0; bysecond=0;',
END_DATE => NULL,
ENABLED => TRUE,
COMMENTS => 'Refreshes Materialized Views on a Scheduled Basis (7:00 PM each day).');
END;
Any suggestions in this regard will be HIGHLY appreciated
Thanks for your time.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Schedular not working [message #306300 is a reply to message #306297] |
Thu, 13 March 2008 10:44  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Are you aware of the fact that in the create statement of a materialized view you can immediately enter the refresh interval? (you'll see that refresh job show up at the dmbs_jobs then).
See reference for details.
Of course I realize that this doesn't mean your code shouldn't work or anything, but it's just a thought.
BTW: did you check if your MV does refresh properly when refreshed manually?
|
|
|
|
| Re: Schedular not working [message #306313 is a reply to message #306288] |
Thu, 13 March 2008 11:22 |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
Manual refresh is working fine but scheduling does not work.
Below Materialized View script
CREATE MATERIALIZED VIEW ASDWLBDB.TEST_TAB
TABLESPACE MV_IVR
BUILD IMMEDIATE REFRESH FAST
ENABLE QUERY REWRITE
AS SELECT SERVICEINSTANCEID,ACCOUNTNO,SERVICENO,PRODUCTLINEID,ACCOUNTNAME FROM ASDWLBDB.TEST_TAB@TEST_LINK;
PLease suggest where to make the changes.
|
|
|
|
|
|
|
|
|
|
| Re: Schedular not working [message #306491 is a reply to message #306288] |
Fri, 14 March 2008 08:50 |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
Below is the output
SQL> show parameter JOB_QUEUE_PROCESSES
NAME TYPE VALUE
------------------------------------ ----------- -------
job_queue_processes integer 10
Any more suggestions please.
|
|
|
|
|
|
| Re: Schedular not working [message #306699 is a reply to message #306288] |
Sat, 15 March 2008 23:09 |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
Hi @Ebrian
I modified the script as per your suggestion but it still does not do the scheduling....
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'IVRDB_REFRESH_test',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'EXEC DBMS_MVIEW.REFRESH(''ASDWLBDB.TEST_TAB'', method => ''F'')',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'freq=daily; byhour=9; byminute=30; bysecond=0;',
END_DATE => NULL,
ENABLED => TRUE,
COMMENTS => 'Refreshes Materialized Views on a Scheduled Basis (9:00 PM each day).');
END;
Any more suggestions please..!!
|
|
|
|
|
|
|
|
| Re: Schedular not working [message #307903 is a reply to message #306288] |
Thu, 20 March 2008 05:33 |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
Hi Ebrian and Barbara,
Thanks for your replies.
I have tried with the below script as well , but no good....
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'IVRDB_REFRESH_test1',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'BEGIN mv_refresh_job(''EXEC DBMS_MVIEW.REFRESH(''ASDWLBDB.TEST_TAB'', ''C'')'');
END;',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'freq=daily; byhour=19; byminute=0; bysecond=0;',
END_DATE => NULL,
ENABLED => TRUE,
COMMENTS => 'Refreshes Materialized Views on a Scheduled Basis (7:00 PM each day).');
END;
IS THERE ANY PARAMETER WHICH NEEDS TO BE SET BEFORE SCHEDULING...
PLEASE SUGGEST WHY THE SCRIPT IS NOT DOING THE JOB.
THANKS FOR YOUR TIME.
|
|
|
|
|
|
| Re: Schedular not working [message #307910 is a reply to message #307903] |
Thu, 20 March 2008 05:54 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
"no good" isn't an Oracle error.
And you still haven't told us what "mv_refresh_job" is and why you don't use the DBMS_MVIEW.REFRESH directly.
Also, the way it looks those multiple double-quotes in that line just look plain wrong, since the resulting quoted String would be 'EXEC DBMS_MVIEW.REFRESH(' and then stop there. That MUST result in some error somewhere.
And FORMAT YOUR POST!
|
|
|
|
|
|
| Re: Schedular not working [message #308802 is a reply to message #306288] |
Tue, 25 March 2008 08:46 |
it_me24
Messages: 167
Registered: March 2006
Location: delhi
|
Senior Member |
|
|
Thanks a TON Everybody.
Finally it worked, i used the below statement which Ebrian had quoted.
JOB_ACTION => 'BEGIN DBMS_MVIEW.REFRESH(''ASDWLBDB.TEST_TAB'', ''C''); END;');
Now, if the refresh fails by any means where can i get the details of which materialised view did not refresh, or refreshed sucessfully what query should i use.
Thanks once again for your valuable response and time spent on resolving my prolonged issue.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
