Home » RDBMS Server » Server Administration » Schedular not working
Schedular not working [message #306288] Thu, 13 March 2008 10:00 Go to next message
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 #306290 is a reply to message #306288] Thu, 13 March 2008 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
it_me24 wrote on Thu, 13 March 2008 16:00
Any suggestions in this regard will be HIGHLY appreciated

You should use smaller font, there it is unreadable.

Regards
Michel

Re: Schedular not working [message #306293 is a reply to message #306288] Thu, 13 March 2008 10:17 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
I do appologies for the same.

please help me resolving the issue.

Thanks
Re: Schedular not working [message #306295 is a reply to message #306293] Thu, 13 March 2008 10:30 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Does USER_SCHEDULER_JOB_LOG.ADDITIONAL_INFO show any errors?
Re: Schedular not working [message #306297 is a reply to message #306288] Thu, 13 March 2008 10:38 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
ADDITIONAL_INFO field is blank in USER_SCHEDULER_JOB_LOG.

please suggest.
Re: Schedular not working [message #306300 is a reply to message #306297] Thu, 13 March 2008 10:44 Go to previous messageGo to next message
skooman
Messages: 912
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 Go to previous messageGo to next message
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 #306314 is a reply to message #306288] Thu, 13 March 2008 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>but scheduling does not work.
My car does not work.

PLease suggest where to make the changes.
Re: Schedular not working [message #306335 is a reply to message #306288] Thu, 13 March 2008 13:56 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
check out JOB_QUEUE_PROCESSES initialisation parameter. If its set to 0, your jobs wont run.
Re: Schedular not working [message #306341 is a reply to message #306335] Thu, 13 March 2008 15:11 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
JOB_QUEUE_PROCESSES isn't used for DBMS_SCHEDULER.
Re: Schedular not working [message #306491 is a reply to message #306288] Fri, 14 March 2008 08:50 Go to previous messageGo to next message
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 #306507 is a reply to message #306491] Fri, 14 March 2008 09:10 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
What exactly is MV_REFRESH_JOB ?

Why not just use EXEC DBMS_MVIEW.REFRESH(''ASDWLBDB.TEST_TAB'', method => ''C'') ?
Re: Schedular not working [message #306699 is a reply to message #306288] Sat, 15 March 2008 23:09 Go to previous messageGo to next message
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 #306773 is a reply to message #306699] Sun, 16 March 2008 12:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Exec is a SQL*Plus command. Exec dbms_mview.refresh ... is not a pl/sql block. A pl/sql block starts with BEGIN and ends with END. Compare what you have to the example in the documentation:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/schedadmin006.htm#i1009099

[Updated on: Sun, 16 March 2008 12:56]

Report message to a moderator

Re: Schedular not working [message #306797 is a reply to message #306773] Sun, 16 March 2008 20:56 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Good point Barbara. I seemed to get carried away with cutting and pasting the OP's code.
Re: Schedular not working [message #307903 is a reply to message #306288] Thu, 20 March 2008 05:33 Go to previous messageGo to next message
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 #307908 is a reply to message #307903] Thu, 20 March 2008 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You seem to be unable to follow OraFAQ Forum Guide:
- Format
- Don't post in upper case

Regards
Michel
Re: Schedular not working [message #307910 is a reply to message #307903] Thu, 20 March 2008 05:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #307943 is a reply to message #307903] Thu, 20 March 2008 07:49 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Try to wrap the code in BEGIN & END tags and remove the EXEC.

JOB_ACTION => 'BEGIN DBMS_MVIEW.REFRESH(''ASDWLBDB.TEST_TAB'', ''C''); END;');
Re: Schedular not working [message #308802 is a reply to message #306288] Tue, 25 March 2008 08:46 Go to previous messageGo to next message
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.
Re: Schedular not working [message #308892 is a reply to message #308802] Tue, 25 March 2008 18:47 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
DBA_MVIEWS
Re: Schedular not working [message #309222 is a reply to message #306288] Wed, 26 March 2008 18:12 Go to previous message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Thanks for your Valuable Support.
Previous Topic: One of my tablespaces is not listed in dba_free_spaces
Next Topic: How to know this table belongs to which tablespace?
Goto Forum:
  


Current Time: Sun Dec 04 04:49:48 CST 2016

Total time taken to generate the page: 0.10000 seconds