Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Muhammad Amjad.

Asked: March 21, 2001 - 1:17 am UTC

Last updated: April 08, 2022 - 2:27 am UTC

Version: 7.x

Viewed 50K+ times! This question is

You Asked

Hi Tom,
can u tell me that how can i scheduled my procedures execution. for example there is a procedure namely 'abc' i want to execute it at 4'o clock every day. it would by highly appreciated if u provide my any code, or reffer documents etc. in this regard.

pl. accept my thanx in advance.

amjad

and Tom said...

DBMS_JOB does this exactly.

to read about dbms_job:

select text
from all_source
where name = 'DBMS_JOB'
and type = 'PACKAGE'
order by line;


In your case, it is very easy:

declare
l_job number;
begin
dbms_job.submit( l_job,
'abc;',
trunc(sysdate)+4/24,
'trunc(sysdate)+1+4/24' );
end;

will run the job at 4am (change 4 to 16 if you meant 4pm, it was not clear) every day.

make sure to set job_queue_processes and job_queue_interval (init.ora parameters).





Rating

  (110 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alex MacDonald, March 22, 2001 - 5:55 am UTC


Values

Gururaj, May 03, 2001 - 12:48 pm UTC

You could have explained the parameteds and provided values to the parameters.

Gururaj

Sudhakar, May 14, 2001 - 4:27 pm UTC


Great Help

Bijay Tuladhar, January 04, 2002 - 4:20 pm UTC

Tom,
I was wondering why my dbms_job wasn't after submitting the job, but as I went through your old cases, from this case, I learnt that I need to set job_queue_interval and job_queue_processes parameter. And it WORKED!

Thank you very much for helping us all.
Bijay


It doesn't work for me!!!!

Sarada Priya, January 13, 2002 - 10:11 pm UTC

My procedure needs to run everyday at 6 AM in the morning and I have scheduled it as follows:

SET AUTOPRINT ON
VAR jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'pcrsadm.upload_ddr_data;', TRUNC(SYSDATE) + 6/24, 'TRUNC(SYSDATE + 1) + 6/24');
COMMIT;
END;
/

It creates the Job for me but never runs it.

Please help me solve this...



Tom Kyte
January 14, 2002 - 7:36 am UTC

make sure to set job_queue_processes and job_queue_interval (init.ora
parameters).



Also, check your ALERT log on the server to see if it has been running but FAILING due to some error.


UNIX CRON

A reader, January 14, 2002 - 12:31 pm UTC

UNIX CRON would be another alternative for this problem.

Tom Kyte
January 14, 2002 - 12:43 pm UTC

If you are running on Unix that would be true.

I personally try to schedule everything I can via dbms_job. First -- it gives me one place to look for all problems (scan the alert and I'm done looking for problems). Second -- I can see when the job last ran, when it'll run again very easily using SQL from anywhere. Third -- the jobs will ONLY run when the database is available. I've seen more then one cron job totally mess up stuff cause it "assumed" the database would be up -- no chance of that here. Fourth -- never the need for a password with dbms_job, with cron -- unless you use OS identified accounts, you'll be putting a password somewhere. And the list goes on....

Yes, cron could do it but I think dbms_job is infinitely superior for something like this. About the only thing I wouldn't use dbms_job for might be a backup (although that is not out of the realm of possibilities ;)



How

A reader, January 14, 2002 - 12:49 pm UTC

Hi, TOm,

How to backup DB by using DBMS_JOB, could you polish that if possible?

Thanks

Tom Kyte
January 14, 2002 - 12:54 pm UTC

A java stored procedure would be able to copy the files easy enough and dynamic sql can issue the "alter tablespace begin/end backup".

INIT.ORA

Jerry, December 10, 2002 - 1:11 pm UTC

THIS FOLLOW UP THAT YOU GAVE SOLVED MY PROBLEM WITH THE JOBS NOT RUNNING :
Followup:
make sure to set job_queue_processes and job_queue_interval (init.ora
parameters).


Thanks

Job with start time in the past

Sasa petkovic, March 05, 2003 - 10:00 am UTC

Hi Tom,

We had interesting situation on our production DB.
We have a job which runs every morinng at 5AM, and it works
perfectly every day.
This morning job run, finished good as usually. After that someone removed this job at 9AM(due to different reasons, no matter what they were) and submited again after half an hour.
But in the definition of the job, start time is in the past(trunc(sysdate)+5/24) and job run once again at 9:30AM.Of course we expected to see this job running tommorrow at 5AM.
According your recommendation : dont believe to anyone, make a proper test case and prove it, I did it and got next:

CREATE TABLE TEST(datum VARCHAR2 (35));

DECLARE
jobno NUMBER;
lstart DATE := TRUNC(SYSDATE) + 5/24 ;
BEGIN
dbms_job.submit(jobno, '<<TEST>>BEGIN INSERT INTO test ( datum) VALUES ( to_char(SYSDATE, ''dd/mm/yyyy hh24:mi'')); commit;END;', lstart , 'trunc(SYSDATE) + 1 + 5/24');
COMMIT;
dbms_output.put_line(jobno);
END;

select * from test

gave me a sysdate in field "datum"

Although is now (in our time zone ) 3PM I got record in table test which proved me that jobs runs with submiting.

I changed pl/sql block for submiting, removed this test job and submited again and got next:

DECLARE
jobno NUMBER;
lstart DATE;
BEGIN
SELECT DECODE(SIGN(SYSDATE - (TRUNC(SYSDATE)+5/24)), 1, TRUNC(SYSDATE)+1+5/24, TRUNC(SYSDATE)+5/24) INTO lstart FROM dual;
dbms_job.submit(jobno, '<<TEST>>BEGIN INSERT INTO test(datum) VALUES (to_char(SYSDATE, ''dd/mm/yyyy hh24:mi'')); commit;END;', lstart , 'trunc(SYSDATE) + 1 + 5/24');
COMMIT;
dbms_output.put_line(jobno);
END;


This time I compared sysdate with usually time for submiting and if sysdate is greater then 5AM then moved running to next day.

This was proved with table "test" and record in it.There was no record in table test with a such submiting.
I can sure you that I read documentation but couldn't find this feature explained.

Thanks for your time.









Tom Kyte
March 05, 2003 - 11:26 am UTC

ops$tkyte@ORA817DEV> exec dbms_job.submit( :j, 'null;', sysdate-5000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> commit;

Commit complete.

ops$tkyte@ORA817DEV> select next_date from user_jobs;

NEXT_DATE
-----------
26-JUN-1989


It doesn't care what the start date is - it just finds everything that "should have run" and runs it.  It is only the INTERVAL that must evaluate to some date in the future. 

Recursive calling

JK, July 09, 2003 - 9:05 am UTC

Hi Tom

I have to schedule a job & rerun the same once it is completed. The job might take anywhere between 30secs to 1min or more. Therefore we cannot schedule it to happen every 45secs. If the job takes 15secs to complete, we want it to kick off again.
Can you suggest me a code snippet for this one please?
Thanks & Regards
JK

Tom Kyte
July 09, 2003 - 11:33 am UTC

what about having the last lines of your routine be:


.....
dbms_job.submit( l_job, 'myself;' );
commit;
end;
/


but you can also just:


create or replace procedure myself( ...., p_NEXT_DATE OUT DATE )
as
begin
......

p_NEXT_DATE := sysdate + 1/24/60/60*45;
end;
/

when you submit the job, use

dbms_job.submit( ...., 'myself(....., NEXT_DATE );' )

have the job itself compute the next_date.

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:9088073430469#9099572884547 <code>

for why and how that works.



Thanks for your quick response

KJ, July 09, 2003 - 2:11 pm UTC

Appreciate your help!

Regards
KJ

What about jobs to be run every hour.

Shailandra, August 13, 2003 - 4:22 pm UTC

Hi Tom,

What will be the syntex of DBMS_JOB to run a job every hour at hour past 15 minutes?

Thanks

Tom Kyte
August 13, 2003 - 7:22 pm UTC

the interval would be:


trunc(sysdate,'hh') + 1/24 + 15/24/60;


take the current time back the "last hour", add 1 hour (1/24), add 15 minutes (15/24/60)

so, at 1:30, that'll return 2:15, at 2:15, that'll return 3:15.



Just what I needed!

A reader, September 01, 2003 - 4:32 pm UTC

Tom,
I'm reading up on dbms_job from O'Reilly's Oracle In a Nutshell, and on this forum. You mentioned the job_queue_interval parameter. The Nutshell book specifies that this parameter is for Oracle 8/8i only. Is there an equivalent parameter for 9i, or is job_queue_processes the only parameter I need to worry about?

Tom Kyte
September 01, 2003 - 5:58 pm UTC

job_queue_processes is the only one in 9i -- the interval is obsoleted in that release.

A reader, October 06, 2003 - 11:37 am UTC


Business Hour Logic

A reader, April 26, 2004 - 5:14 pm UTC

Hi,

Is there any way I can say that the job should be run between 9 AM - 5 PM everyday, at the intervals of 15 minutes.

Please suggest

Tom Kyte
April 27, 2004 - 4:28 am UTC

do you have my book "Expert one on one Oracle"?

I describe many ways to schedule jobs in there.


but here:


exec dbms_job.submit( :n, 'null;', sysdate, 'case when to_char( sysdate, ''hh24'' ) between ''09'' and ''04'' then sysdate+5/24/60 else trunc(sysdate)+1+9/24 end' );


that should work -- use CASE to say "if between 9am and 4:59:59pm" return 5 minutes later, else tomorrow

Job scheduling

Giri, April 30, 2004 - 11:42 am UTC

Tom,
I would like to schedule job every sunday at 1.00 AM. can you please help me how to schedule?.

Thanks,
Giri

Tom Kyte
April 30, 2004 - 5:39 pm UTC

dbms_job.submit( l_job, 'whatever;',
trunc(next_day(sysdate,'SUN'))+1/24,
'trunc(next_day(sysdate,''SUN''))+1/24' );



Scheduling every hour

Ravi, June 14, 2004 - 5:57 am UTC

how do I reschedule a job every hour, have tried this, but aborts with an error.

DECLARE
JOBNO NUMBER(10,3);
BEGIN
DBMS_JOB.SUBMIT(job => jobno,
what => 'recompile_invalid_objects;',
next_date=> sysdate,
interval=> to_date(
to_char((sysdate + (((1/24)/2))),'DDMMYYYY HH24MISS')
, 'DDMMYYYY HH24MISS')
);
commit;
dbms_output.put_line(jobno);
END;
/

DECLARE
*
ERROR at line 1:
ORA-23319: parameter value "14-JUN-2004" is not appropriate
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 129
ORA-06512: at line 4

Tom Kyte
June 14, 2004 - 8:03 am UTC

interval is a string, a string that is evaluated when the job is run.

you are sending "a date"
not a string that is a function that would return a date when executed later.


interval => 'trunc(sysdate,''hh'')+1/24'

is a string that when evaluated will return the top of the next hour, everytime it is evaluated.


but I'm fascinated by the name of the procedure.  does it really "recompile invalid objects".  If so, here is the best way for you do schedule this job:


SQL> 



that is, don't.  it is not necessary.  invalid things will fix themselves.  Rather what you should be doing is asking or researching "why in my stable production environment are things going invalid since they should only do that when a big change is made" 

run job once or once per startup

j, June 21, 2004 - 9:02 am UTC

hi tom,

we want to get a job configured to be run only once or once per instance startup.

the only solution we 've found out so far is to provide two different procedures for argument "what" of dbms_job.submit: the procedure for starting on every instance startup sets 'next_date' to sysdate, the other just removes the job.

is their another/better way to acchieve the same goal?

Tom Kyte
June 21, 2004 - 9:35 am UTC

a database startup trigger? "after startup on server...."

but this would be DDL

j, June 21, 2004 - 9:52 am UTC

(then we would prefer our "method")

Tom Kyte
June 21, 2004 - 1:51 pm UTC

you asked for another/better way.

database startup trigger is another, best way.

anything else is a hack you will have to put together.

are jobs preserved after reboot?

Ilya, August 09, 2004 - 6:17 pm UTC

Hi Tom,

I have a stupid question that I couldn't find an answer to: after I submit a job, if the database is restarted (i.e., someone reboots the server machine), while the job still be there and run when scheduled or will I have to resubmit it?

I noticed that Enterprise allows to schedule jobs from the Enterprise Manager. Is submitting a job using dbms_job equivalent to that?

If it's relevant, I'm running 9.2.0.1

thanks,
Ilya

Tom Kyte
August 09, 2004 - 8:50 pm UTC

if you

a) submit a job using dbms_job
b) commit it

it'll be there regardless - it is just data in a table at that point and committed data in tables lives over database failures/restarts...


EM in 9i uses its own stuff, in later releases it is moving to using the more "sophisticated" scheduler in the database.


job_queue_interval parameter

A reader, August 10, 2004 - 4:58 pm UTC

Hi Tom,
I am using 9ir2, when I do
SQL> show parameter job


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10

I don't see job_queue_interval parameter, how do I set up?
Regards 

Tom Kyte
August 10, 2004 - 7:26 pm UTC

you do not, obsoleted in later releases.
job queue processes is all you need.

A single job that runs exactly at 55,10,25,40 past the hour

Richard Berry, August 31, 2004 - 2:23 pm UTC

Hi Tom...

The interval of sysdate + 15/24/60 does not take into account the time it takes the procedure to execute, thus job start time will creep away from the actual starttime. Its important for this job to complete by the 15's so that a cron job can pick up the output.

I created an interval that would work; however, its way too ong. Looks like the interval can only be 200 char.

The case statement below is what I based the interval on...

select
case
when to_char(sysdate,'mi') between 0 and 9
then trunc(sysdate,'hh') + 10 /24/60 -- 10 min past hr
when to_char(sysdate,'mi') between 10 and 24
then trunc(sysdate,'hh') + 25 /24/60 -- 25 min past hr
when to_char(sysdate,'mi') between 25 and 39
then trunc(sysdate,'hh') + 40 /24/60 -- 40 min past hr
when to_char(sysdate,'mi') between 40 and 54
then trunc(sysdate,'hh') + 55 /24/60 -- 55 min past hr
when to_char(sysdate,'mi') between 55 and 59
then trunc(sysdate,'hh') + 1/24 + 10 /24/60 -- 10 min past NEXT hr
else sysdate
end
from dual;

Can you think of an Interval statement that is 200 char or less that would do the same thing. Thanks!

-Richard

Tom Kyte
August 31, 2004 - 2:58 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4920931320207

or

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1824650899163 <code>
(if you have "Expert one on one Oracle" -- see dbms_job in the appendix for more details on that approach)

ora-23319 error

Lisa, January 10, 2005 - 8:24 am UTC

I tried to adapt the info the person with the ora-23319 error had as I am receiving the same error but it still gives me the error. Here is my adaptation for a job I want to have executed on the first business day of the month:

DBMS_JOB.SUBMIT(Job_No, 'FLDE_Utilities.Poll_For_File('''||p_Data_Source||''');', SYSDATE,
'TRUNC((LAST_DAY(SYSDATE) + 1) + Date_Utilities.IsWeekend(LAST_DAY(SYSDATE) + 1) + '||TO_CHAR(Start_Parameters.Polling_Window_Start, 'HH24')||'/24');
COMMIT;

where Date_Utilities.IsWeekend is a function that adds the number of days to the first of the month to get the first business day. I get the following same error:

ERROR at line 1:
ORA-23319: parameter value is not appropriate
ORA-06512: at line 1
ORA-06512: at "FLDEADMIN.ERROR_UTILITIES", line 53
ORA-06512: at "FLDEADMIN.ERROR_UTILITIES", line 26
ORA-06512: at "FLDEADMIN.BATCH_UTILITIES", line 50
ORA-23319: parameter value "TRUNC((LAST_DAY(SYSDATE) + 1) + Date_Utilities.IsWeekend(LAST_DAY(SYSDATE) + 1) + 14/24" is not appropriate
ORA-06512: at line 1


Tom Kyte
January 10, 2005 - 9:06 am UTC

do you have access to Expert One On One Oracle? if so, see the appendix on dbms_job -- i cover these next two things in detail



two things

a) 'FLDE_Utilities.Poll_For_File('''||p_Data_Source||''');'

is a bad idea, it should just be:

'FLDE_Utilities.Poll_For_File(p_job=>JOB);'

and the first line of poll_for_file should be:

select * into L_REC from poll_for_file_parameters where job = P_JOB;

that would be "shared pool friendly"


b) you can use custom job scheduling -- since I have NO IDEA what date_utilities is or does, you can:

'FLDE_Utilities.Poll_For_File(p_job=>JOB, p_next_date => NEXT_DATE );'


and the last line of your procedure can be:

p_next_date := TRUNC((LAST_DAY(SYSDATE) + 1) +
Date_Utilities.IsWeekend(LAST_DAY(SYSDATE) + 1) + 14/24;

then your dbms_job.submit would just be:

dbms_job.submit( job_no,
'FLDE_Utilities.Poll_For_File(p_job=>JOB, p_next_date => NEXT_DATE );' );

insert into poll_for_file_parameters ( job, input )
values ( job_no, p_data_source );








Lisa, January 10, 2005 - 10:21 am UTC

I did look at the appendix and did not find it helpful in this case. At the moment I would prefer not to submit the job as you suggest in part a of the response. Can you offer a response that gives the solution in the manner in which I want to submit it?

Tom Kyte
January 10, 2005 - 11:08 am UTC

geez, "nope, don't want to do it easy or right". neat. I tried to give a response that solved your problem and makes your system better overall.

(when did this wind up being "shut up and write my code the way I'm thinking you should even though it is the wrong way" anyway)

And actually, you never in fact "specified a manner in which you want to submit it". You gave me a non-working example. I gave you a working solution.

count your (parenthesis). see if you see what I see.

Lisa, January 10, 2005 - 1:02 pm UTC

You do not know what constraints or situation I am operating under, work or otherwise, and I do not feel that I should have to elaborate on them in order to get a useful response. A more polite response would have been to answer my question with the caveat that you did not agree or support my approach. I would have appreciated that more.

Tom Kyte
January 10, 2005 - 2:08 pm UTC

And you?

You asked a question, well, in fact not even a question. You made a statement:

a) i tried something
b) it did not work

So, I made reasonable assumption

c) you wanted to know how to get it going in the best way.

which I proceeded to do. You specified no requirements, no pre-conditions, nothing. As if I were to read your mind "ah hah, she wants me to do it like this obviously".... to which you reply:

...
Can you offer a response that gives the solution in the manner in
which I want to submit it?
......

without really spelling out in the first place what that is. And actually, I don't like to give answers in the manner in which people want to submit things -- I want to give the best practice answer. (long history of that here, most of my answers started with "why do you think you want to do that"

Sooooo, there you go.

You do not know what constraints or situation I am operating under, work or otherwise as well. You should have to elaborate *on your specific needs* -- what it is you want (cannot read minds). You were very terse -- if you look back. I gave you what I thought was the best way to implement this - much more readable and maintainable. Fine if you don't want to do that.

Whatever.

and it all came down to mis-matched parentheses.



Job_queue_interval

Steve Kiteley, April 15, 2005 - 5:40 am UTC

With job_queue_interval now no longer usered what is the minimum 'granularity' for job the job queue 'waking up'. If I set a job to repeat, say, every 1 minute 20 seconds how accurately can I expect this to happen and what might influence this accuracy? The O9 documentation tells us that job_queue_interval is no longer required but I can't find any explanation as to what functionality has replaced it



Tom Kyte
April 15, 2005 - 9:14 am UTC

_job_queue_interval is set to 5 in 9ir2 and 10gr1.

RE job_queue_interval

Vinayak, April 15, 2005 - 10:50 am UTC

Metalink Note 197220.1

Oracle Server 8.1 uses initialization parameter JOB_QUEUE_INTERVAL to specify
how frequently each SNPn background process woke up. The parameter
JOB_QUEUE_INTERVAL is obsolete with Oracle Server 9.0.

For each Oracle Server 9.0 instance, job queue processes are dynamically
spawned by a coordinator job queue (CJQ0) background process. The coordinator
periodically selects jobs that are ready to run from the jobs shown in the
DBA_JOBS view.

The Oracle Server 9.0 default interval upon which the job queue coordinator
wakes up to see if there are any jobs to run is 5 seconds.
If 5 seconds is inappropriate interval, you can set a hidden parameter:
_job_queue_interval to a value other than 5 seconds. The lowest value it will
accept is 1 second.

DBMS_JOB on selected days

Bill K, May 27, 2005 - 11:12 am UTC

I have a job that needs to run on Tuesday and Thursday mornings. Right now I have set the job to run at 7am every morning. Then I have inlcuded code to find out what day it is:

IF TO_CHAR(sysdate, 'DY') = 'TUE' OR TO_CHAR(sysdate, 'DY') = 'THU' THEN
runThisProcedure;
END If;

Is there a way to select only Tuesday and Thursday when I submit the job without having to write the IF statement. Thanks!

Tom Kyte
May 27, 2005 - 12:35 pm UTC

interval of

least( next_day( sysdate,'tue'), next_day(sysdate, 'thu') )

should do it. BUT if the job didn't get to RUN on thu (the job queues were too busy) then we'd try to run it as soon as possible after thu. To solve that, you would be back to the "if" statment in the code (because even though a job is scheduled to run on thu, if we don't get to it until fri, that is when it'll run)

Dave, July 28, 2005 - 7:08 am UTC

I dont have your book here so cant check out the dbms_job section

got a requirement to run a job between 2am and 10pm monday to friday every 10 minutes

so I took your previous example and got this

dbms_job.submit(v_job_nbr,'null;',
sysdate,
'case when to_char( sysdate,''hh24'' ) between ''02'' and ''22'' then sysdate+10/24/60 else trunc(sysdate)+1+2/24 end');

That deals with the 2am to 10pm part I think. Having trouble adding in the mon to friday bit, came up with this but it doesnt work

exec dbms_job.submit( :n, 'null;', sysdate, 'case when to_char( sysdate,
''hh24'' ) between ''02'' and ''22'' and case when to_char(sysdate,''D'' ) between ''02'' and ''06'' then sysdate+10/24/60 else
trunc(sysdate)+1+2/24 end' );

Doesnt like the interval there, probbaly because i can't to and case...

any ideas?

Thanks

Tom Kyte
July 28, 2005 - 9:12 am UTC

well, it isn't really about dbms_job, but rather "a function to return a time given an input"...


I think:

case
when to_number(to_char(sysdate,'hh24')) between 2 and 21
then sysdate+10/24/60
when to_char(sysdate,'DY') in('MON','TUE','WED',THU')
then trunc(sysdate)+1+2/24
else next_date(trunc(sysdate),'MON')+2/24
end


if the hours are between 2am and 9<something>pm, then return 10 minutes from now

else (it isn't between 2 and 9 something), and it is during the week, return tomorrow at 2am

else (it isn't between 2 and 9 something -- and it isn't during the week) return next monday at 2am

DBMS_JOB and Daylight Transition

Juan Velez, August 26, 2005 - 2:44 pm UTC

First, I have Oracle 9i. (Can not use DBMS_SCHEDULER).

How do I schedule a job to run even on the fall transition? My db is set to CPT (Central Prevailing Time). On October 30th this year, it will go from 1:59:59 AM CDT to 1:00:00 AM CST.

If I have a job that needs to run every five minutes starting at the top of the hour (hh:00, hh:05, ..., hh:55), how do I do to run it during fall transition?

Assuming my job NEVER takes more than 1 minute to complete, and I use 'trunc(sysdate,'mi')+(5/1440)' for the interval, then when it is time to execute the October 30th 1:55 AM CDT interval (Oracle does not know about the CDT portion for DATE datatypes), it will reschedule itself to run on October 30th at 2:00 AM, skipping all 5 minute intervals in 1:00 CST.

Similar situation with spring transition (1:59:59 CST, 3:00:00 CDT)

Is there TIMEZONE-aware version of DBMS_JOB?

Thanks in advance for the advice

Juan A.

Tom Kyte
August 26, 2005 - 4:23 pm UTC

it'll just work - we use the system clock like anything else -- as long as you are based on sysdate, which is read from the clock, it'll always be 5 minutes in the future.

DBMS_JOB and Daylight Transition

Juan Velez, August 26, 2005 - 4:52 pm UTC

Unfortunately it does not. At 1:55AM CDT, it will be rescheduled to run at 2:00 AM, that means that we will have to wait 65 minutes to see this job run, instead of waiting 5 minutes to see it run at 1:00 AM CST. (after 1:59:59 AM CDT, you see 1:00:00 AM CST. )

select to_date('200510300155','yyyymmddhh24') + 5/1440 as nextDate
from dual;

NEXTDATE
--------
30-OCT-2005 2:00:00 AM


Tom Kyte
August 27, 2005 - 5:01 am UTC

ahh, right, now I see. Interesting problem. At 1:55am on that sunday, you cannot schedule it for any time in the "past" and that is what you would need to do, you need it to run at 1am one that sunday at 1:55am on that sunday, the first time you see 1:55am.

And even if you "could", it won't run in five minutes, it would tend to run immediately (since it's scheduled runtime has past).


The fallback will not work on a 5 minute interval, the spring forward will (at 1:55 you ask to have it run at 2am, at 2am it becomes 3am and since the job is past due, it will run)

Hope this doesn't sound too convoluted but what you could do is schedule another job that runs once a year. It's only goal in life - fix this job. You schedule this job to run at say 1:59 AM, it'll look to see if the job is scheduled after 2am and dbms_lock.sleep until 2am would have past (it is now 1am) and then use next_date to "fix" the job.

does that make sense?

Scheduling job different times on different days

A reader, September 06, 2005 - 7:14 pm UTC

Tom,
Is it possible to set up a DBMS_Job that it can run in the following intervals
run every hour on Monday, Tuesday, Wednesday, Thursday and Friday and
run every 6 hours on Saturday and Sunday.

Thank you



Tom Kyte
September 06, 2005 - 9:25 pm UTC



you can use case in your interval

case when to_char(sysdate,'d') in (7,1)
then trunc(sysdate,'hh') + 6/24
else trunc(sysdate,'hh') + 1/24
end

(note: 7,1 are "NLS" dependent - check your sat/sun 'd' values)

scheduling formscfg config with dbms_job

ian gallacher, September 20, 2005 - 5:44 am UTC


Hi Tom,

I am new to the world of dbms_job .

I require to schedule the running of a form once a day – not exactly ground breaking – in a App server windows server 2000 environment. I have achieved this by windows scheduling but feel a neater solution would be to use dbms_job. ( since scheduling re-acts differently from XP (my dev) to server 2000)

I am I on the right track or stick with windows scheduling ?

Program.bat is typically
explorer "</code> http://iansdev:8888/forms90/f90servlet?config=sci_dc" <code>

I can execute the batch file from sql as
Host program.bat

Can the host command be used in a procedure so as to be available to dbms_job ?

Thanks for your attention and any comments

Ian


Tom Kyte
September 20, 2005 - 10:13 am UTC

it would not be "neater", dbms_job is good for running a job in the database

forms runs outside the database.


you would either use the os scheduler as you are or if you are using the Enterprise manager framework - you could use that job scheduler as well.

dbms_jobs

Raghavarao.RV, September 21, 2005 - 2:07 am UTC

Hi Tom

If there are 10 jobs scheduled and if the number of job_queue processes is 5 whether all the jobs will run or only first 5 jobs only will run? Or is that mean the number given in job_queue_process is the number of jobs that can run at a time parallelly? Please clarify.

Thanks in advance
Raghav



Tom Kyte
September 21, 2005 - 6:57 pm UTC

only 5 can run, you only have 5 processes to process jobs.



job_queue_processes

A reader, September 21, 2005 - 1:41 pm UTC

job_queue_processes tells the number of that can run concurrently. In your case 5 jobs. Once one of those 5 jobs complete, it will take up the next one waiting till all the 10 are done.

So five is the max that can run concurrently in your case. It is like the number of checkout counters.

Dave, October 05, 2005 - 9:58 am UTC

Hi Tom, in a past review here (</code> http://asktom.oracle.com/pls/ask/f?p=4950:61:10772537996192616404::::P61_ID:388479262167#45054803943674 <code>

i asked about returning an interval where it would run Monday to friday 2am to 10pm and you gave me a case statement.

Well I have spent ages over and 'just dont get it' I dont understand where to implement that case statement (in a function, or in the dbms_job.submit call)

I looked at your 8i one on one book and saw the technique for returning next_date in the 'what' part of the job. Unfortunately i cannot use that since it is 3rd party code which is untouchable

can you provide me more info on where I use your statement (and how the next_date came into it in your example)

Thanks

Tom Kyte
October 05, 2005 - 11:36 am UTC

... where to 
implement that case statement (in a function, or in the dbms_job.submit call) ...


you choose, you can do EITHER.  In this case however, the interval string is too long, so we'll use a function (and I had a typo, next_date should be next_DAY)


ops$xp8i\tkyte@ORA8IR3W> create or replace function next_run return date
  2  as
  3          l_date date;
  4  begin
  5
  6          execute immediate '
  7          select case
  8                 when to_number(to_char(sysdate,''hh24'')) between 2 and 21
  9                     then sysdate+10/24/60
 10                     when to_char(sysdate,''DY'') in(''MON'',''TUE'',''WED'',''THU'')
 11                     then trunc(sysdate)+1+2/24
 12                     else next_day(trunc(sysdate),''MON'')+2/24 end
 13            from dual' into l_date;
 14
 15          return l_date;
 16  end;
 17  /

Function created.

ops$xp8i\tkyte@ORA8IR3W>
ops$xp8i\tkyte@ORA8IR3W> select next_run() from dual;

NEXT_RUN()
--------------------
05-oct-2005 11:39:53

ops$xp8i\tkyte@ORA8IR3W>
ops$xp8i\tkyte@ORA8IR3W> declare
  2          l_job number;
  3  begin
  4          dbms_job.submit( l_job, 'null;', sysdate, 'next_run()' );
  5  end;
  6  /

PL/SQL procedure successfully completed.
 

Jobs failed

Peter, October 14, 2005 - 1:34 pm UTC

Hi Tom,

I have a very strange case here.
The procedures can be run at sql/plus enivorment. but not run or failed when using DBMS_JOB.submit.
here is what I did.

log in to a database 'product' using username 'user'

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> CREATE TABLE USER_ERROR_LOGS (
         error_code varchar2(20),
         error_message varchar(20),
         program             varchar(20),
         fail_time         date
        );
table created.

SQL> CREATE OR REPLACE PROCEDURE USER_TEST_PROC
AS
BEGIN

         FOR lv_err IN 200507..200508  
         LOOP        
                 INSERT 
                 INTO  USER_ERROR_LOGS
                SELECT TO_CHAR(lv_err),
                             'ERROR_'||lv_err,
                             'USER_TEST_PROC',
                             SYSDATE
                FROM
               schema_name.table_t@develop a  -- public dblink 
              WHERE ROWNUM<2;
             COMMIT;
        END LOOP;

EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
RAISE;
END;
/

PL/SQL procedure successfully completed.


SQL> EXEC USER_TEST_PROC;
PL/SQL procedure successfully completed.

SQL> SELECT error_code, error_message, program, TO_CHAR(fail_time, 'MM/DD/YYYY HH24:MM:SS') FROM USER_ERROR_LOGS;
ERROR_CODE        ERROR_MESSAGE                     PROGRAM                                 FAIL_TIME 
-----------        ---------------                 -------------------        ---------------
200507                ERROR_200507                     USER_TEST_procedure        10/14/2005 12:50:41 
200508                ERROR_200508                     USER_TEST_procedure        10/14/2005 12:50:47 

SQL> delete from user_error_logs;

2 rows deleted.

SQL> commit;

Commit complete.

SQL>  DECLARE 
                lv_job1 PLS_INTEGER;
                BEGIN
                DBMS_JOB.SUBMIT(lv_job1, 'USER_TEST_PROC;', SYSDATE, NULL);
                COMMIT;
                END;

PL/SQL procedure successfully completed.

SQL> SELECT job, what, broken, Failures, total_time from user_jobs;
job         what                                broken       Failues     total_time
------------------                -------      --------  -----------
116        USER_TEST_PROC;            N                    2                    0

** Job 116 failed here !

SQL> SELECT * FROM USER_ERROR_LOGS;
no rows selected


In order to get rid of the role issue.
I tried the following 

SQL> set role none;

Role set.

SQL> EXEC USER_TEST_PROC;

PL/SQL procedure successfully completed.

SQL> SELECT error_code, error_message, program, TO_CHAR(fail_time, 'MM/DD/YYYY HH24:MM:SS') FROM USER_ERROR_LOGS;
ERROR_CODE        ERROR_MESSAGE                     PROGRAM                                 FAIL_TIME 
-----------        ---------------                 -------------------        ---------------
200507                ERROR_200507                     USER_TEST_PROC                    10/14/2005 12:57:51 
200508                ERROR_200508                     USER_TEST_PROC                    10/14/2005 12:57:57 


** THE procesure can run without role issue !

SQL>  set role all;

Role set.

SQL> 

SO,
THE PROCEDURE DOES RUN in SQL PLUS 
THE job submited by DBMS_JOB does not run or  failed


I  belive it maybe the dblink problem. 
IF I replace 
     schema_name.table_t@develop a
 with 
          schema_name.table_t a  -- in same database instance  not a dblink

in our procedure
        
Procesdures works in both sql/plus and DBMS_JOBS.

Could you explain why the behavior like this?  

I am a developer not a DBA. I told our DBA there are something wrong with the DBlink. 
But I need to have a reason to convince DBA what is wrong with the DBlink.

the dblink is 
create public database link develop
using 'develop'; 


Your help is highly apprecaited.

Have a nice weekend !


 

Tom Kyte
October 14, 2005 - 5:35 pm UTC

what is it failing with ? (alert log will tell you)


when you connect to test - is that over the network or "locally" - I'm suspicious of the database link - and possibly having more than one tnsnames.ora on the server, one of which is seen by the job queues and a different one seen by either the listener or your directly forked off dedicated server.

REF.JOB Failed

Peter, October 14, 2005 - 4:54 pm UTC

addition information;

 If I force the job to run, it can be finished sucessufully.

SQL> EXEC DBMS_JOB.RUN(116);

PL/SQL procedure successfully completed.


Thanks,
 

Tom Kyte
October 14, 2005 - 6:05 pm UTC

that makes me think "more than one tnsnames.ora on the server" even more.


when run by the job queues - I believe "tnsnames.ora file #1" is being used and the database link fails to resolve.

when run by you - "tnsnames.ora file #2" is being used and the database link does resolve.

Nice

Raju, October 15, 2005 - 8:38 am UTC

Hello Tom,
I have a procedure which does a delete and I want to run it as a job only once and on 30 October.
How to specify interval for that??

Please help.
Bye!!



Tom Kyte
October 15, 2005 - 9:55 am UTC

the start date would be the next 30th of october and the interval could be

add_months( trunc(sysdate,'dd'), 12 )

REF: Jobs failed

Peter, October 20, 2005 - 4:31 pm UTC

Hi Tom,

Thanks for the quick answers. We are still not able to indentify the issues.

I am not a DBA and can not see the alert log.

The both database instances are remote database instances in the same unix box.

The DBA checked the TNSNAMES.ORA and there is only one oracle home and one TNSNAMES.ORA file.

Is there other reasons could cause that?

Thanks !







Tom Kyte
October 21, 2005 - 7:45 am UTC

ask dba to look at alert log for you then.


I still believe there are more than one. If the dba had time to do a FIND on the entire file system, they could have taken 30 seconds to peek at the alert log.

REF: Jobs Failed

Peter, October 21, 2005 - 2:59 pm UTC

Hi Tom,

After looking the alert Log, we found this:

ORA-12012: error on auto execute of job 116
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from develop
ORA-06512: at "USER.USER_TEST_PROC", line 24

How could that happened for job auto executing?

The Job can be forced to run at user session. it should be no issue with system previlege.

Could you please explain this?

Thanks !



Tom Kyte
October 22, 2005 - 10:28 am UTC

ahh, the public database link - does it include a user/password OR NOT?

If not, there is no "user password" in the job queue - but there is when you log in yourself.

REF: jobs Failed

Peter, October 24, 2005 - 1:03 pm UTC

Hi Tom,

Thanks for the quick response.

As far as I know there is no username/password in the public dblink.

When the Job submitted into the job queue, who is going to run the job and what system privilegs needed to run the job as a background process?

Thanks,





Tom Kyte
October 24, 2005 - 1:16 pm UTC

You'll need them (user/password) associated with the dblink, else there "aren't" any credentials to be sent to the remote site! The only thing the job knows is "your username", it hasn't any idea about "your password".



REF: Jobs Failed

Peter, October 24, 2005 - 3:00 pm UTC

Hi Tom,

Thanks for the quick reply.
The DBlink was created by DBA for public use. No username and password associated with it.

create public database link DEVELOP using 'develop';

How could I resolve this issue now?

Thanks !




Tom Kyte
October 25, 2005 - 1:14 am UTC

ask them to create you a dblink that can be used with the job if you need it to be used with the job

Job_que_processes

Raghav, November 04, 2005 - 10:05 am UTC

Hi Tom

We have configured the number of job queue processes to 5 as in my previous question.  Whenever I run one / two jobs physically (not by the system as per frequency given), by 
SQL> "exec dbms_job.run(101); ---101 is job number.
then I found that the job_queue_processes is becoming zero(0).  If by chance if I didnt update it back to 5, all the processes are stopped and no process run as per the schedule. What may be the reason? Shouldn't we run the process physically? Cant we keep the above processes figure permanent? What should we do to let it not changed? What to do if the job is run physically, the same job will have to run automatically as per the scheduled time?

Thanks in Advance
Raghav 

Tom Kyte
November 04, 2005 - 5:15 pm UTC

I have never seen this, can you cut and paste

show parameter job_queue
exec dbms_job.run(101)
show parameter job_queue

to show this happening - a DIRECT cut and paste, nothing else.

Init.ora parameter for job scheduler in 9i

Hugues Sauzier, November 07, 2005 - 11:59 am UTC

Very good knowledge sharing. We did not know what was the parameter to set to start the Oracle job scheduler. Now we know.

dbms _job queue processes

Raghav, November 11, 2005 - 8:31 am UTC

Hi Tom

When I use the command you have given, it is giving the following error, Please let me know whether I have used the command corrctly or not.

SQL> show parameter job_queue
unknown SHOW option "parameter"
unknown SHOW option "job_queue"

The problem which I explained earlier is happening in two cases.
1) When I restart the server
2) When I execute the procedure directly through SQL (without the auto job queue)

One doubt I have regarding cut and pasting the contents in this forum.  Can I take a screen shot of the server (where it shows job queue processes) and paste in this. Will it not be a problem? I am asking this question because, I have not seen any such screen shots in this forum and I only see the links to such screens.

Thanks and Regards
Raghav
 

Tom Kyte
November 12, 2005 - 8:30 am UTC

you are using a really old sqlplus I guess.


you have to use TEXT on this site, I am very much graphically challenged. It is all about the courier font here.


it sounds simply like you have job queue processes set to zero in your init.ora, everytime you restart the database - it'll go back to being zero again.

Job queue processes

Raghav, November 21, 2005 - 7:46 am UTC

Hi Tom

We are using Oracle 9i and I have gone through the init.ora file for the entry job queue processes but I didnt find it. If I want to insert the entry is the following syntax correct? (I want to confirm as I need to do the change in production environment).

job_queue_processes = 10

if not, please let me know what is the syntax.

Thanks and Regards
Raghav

Tom Kyte
November 21, 2005 - 8:53 am UTC

you will test this in test first won't you? then you'll know.


yes, that is right.

DBMS_JOB vs CRON

Jagjeet Singh, December 25, 2005 - 6:39 am UTC

Hi,

I am also interested to put all the database related things in dbms_jobs instead of cron.

Can we achieve this using dbms_job ?

Need to refresh one schema in target from source database.

o In source this schema's jobs are broken. --Intentionaly
o export that schema.
o import into target and all jobs are out from broken stage. which should not happen in this case.
o can not set job_queue_processes=0 as other jobs from diff. schemas already running.

Using cron it is easy ..

Thanks,
Js

Thanks tom..was really helpful

Melissa, August 18, 2006 - 11:06 am UTC


statspack every 15 minuts

Yoav, September 11, 2006 - 3:52 pm UTC

Hi Tom,
I tried to schedule my statspack to run every 15 minute.

select sysdate,trunc(sysdate)+ (trunc(to_char(sysdate,'sssss')/ 900)+1)*15/2
4/60 from dual;

SYSDATE TRUNC(SYSDATE)+(TR
------------------ ------------------
11-sep-06 22:41:11 11-sep-06 22:45:00

1 row selected.

variable y number
begin
2 dbms_job.submit
3 ( :y, 'take_snap;', sysdate,'trunc(sysdate)+ (trunc(to_char(sysdate,''ssss'')/ 900)+1)*15/24
/60'
4 );
5 end;
6 /
begin
*
ERROR at line 1:
ORA-23420: interval must evaluate to a time in the future
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 134
ORA-06512: at line 2

Why did i got this error ?
I gust showed that the next interval should be at 22:45:00
and now it 22:41:11 ?

Thank You Tom.

Tom Kyte
September 11, 2006 - 4:16 pm UTC

why not just

sysdate+1/24/60*15

to go every 15 minutes (who cares if it "slides" a bit).  

sssss - 5 s's is seconds past midnight

ssss - 4 s's is just "seconds seconds"


ops$tkyte%ORA10GR2> select to_char(sysdate,'ssss'), to_char(sysdate,'sssss') from dual;

TO_C TO_CH
---- -----
4545 58365
 

Materialized View refresh schedule

A reader, November 28, 2006 - 6:18 pm UTC

Tom,

I have a materialized view created on another remote materialized view (remote database).

The remote Materialized view gets refreshed every 30 minutes.

I want to schedule refresh with out overlapping with the remote MV refresh. How do i schedule it ?

MV refresh completion takes just 5 minutes.

Tom Kyte
November 28, 2006 - 8:08 pm UTC

why do you care?

A reader, November 28, 2006 - 9:10 pm UTC

<< why do you care?

Because if my local materialized view refreshes around or at the same time when the remote materialized view refreshes, my local materialized gets 0 records count.

That zero record count is because of overlapping of refresh schedule with remote MV refresh. If i do local refresh after few minutes, i was able to get the expected result. How do i set the refresh interval using dbms_job ?

Local materialized and remote both does complete refresh.


Tom Kyte
November 30, 2006 - 8:39 am UTC

make the remote mv refresh transactionally then, so the data NEVER disappears.

Good suggestion. Thanks

A reader, November 30, 2006 - 11:09 am UTC


DBMS JOB

Kshama Tikmani, January 05, 2007 - 2:24 am UTC

Hi,

I am using Oracle 9i.I have a dbms job running in my application since last 1yr and it is scheduled to run every 5mins.It never had any problem but suddenly yesterday i checked that it has stopped running. although it was scheduled automatically to run after 5mins it did not execute at schedule time.Strange.. I could not figure out why this has happened? Although due to urgency i ran it manually and it completed successfully.Again it got scheduled to run after 5mins but didn't work.
Please advise.

Thanks..
Kshama
Tom Kyte
January 05, 2007 - 10:00 am UTC

perhaps someone changed your job queue processes, did you look to see if they are enabled. also, review your alert log.

DBMS JOB

Kshama, January 18, 2007 - 2:00 am UTC

Hi Tom,

No one changed the job queue proces.Also i checked it wa senabled. Also no traces in alert log.

Need to schedule job for 11:30pm daily

Eric, February 01, 2007 - 2:42 pm UTC

I've tried to format the job schedule based on the details in the previous notes, but I'm not sure I'm getting this correct. I'm trying to schedule the execution of a package for 11:30pm daily. Here's what I think it should be:

declare
l_job number;
begin
dbms_job.submit( l_job,
'onesourceadapter_pkg;',
trunc(sysdate)+22/24+30/24/60,
'trunc(sysdate+1)+22/24+30/24/60' );
commit;
end;

I wanted to verify the times so I executed the following select, but the results are not what I expected.

select to_char(trunc(sysdate)+22/24+30/24/60,'MM/DD/YYYY HH24:MM:SS') from dual;

TO_CHAR(TRUNC(SYSDA
-------------------
02/01/2007 22:02:00

1 row selected.

What am I doing wrong?
Tom Kyte
February 01, 2007 - 3:27 pm UTC

MM is Month and this is feb :) month = 02

MI is Minutes


oh, and I've never made that mistake, nope, no way :)

scheduling an oracle job

Eric, February 01, 2007 - 4:28 pm UTC

Opps... that corrected my output.

select to_char(trunc(sysdate)+22/24+30/24/60,'MM/DD/YYYY HH24:MI:SS') from dual;

TO_CHAR(TRUNC(SYSDA
-------------------
02/01/2007 22:30:00

1 row selected.

Thanks.

schedule job

Pankaj, February 03, 2007 - 2:38 am UTC

hi tom,
I want to schedule job at 5th of every month,
how can schedule this.
thanks
Pankaj

Tom Kyte
February 03, 2007 - 7:50 pm UTC

add_months( trunc(sysdate,'mm'), 1) + 4

take today,
make it become the first day of the current month,
add one month,
and then add 4 days.

batch job scheduling

cmmurari, March 22, 2007 - 8:37 am UTC

Hi ,
I have Big Oracle procedure lines of 7000lines. inside procedure i have dbms_ouput statments.
When we submit oracle procedure using dbms_job. Where can i see output values?? is oracle creates log any where.?.
if i run procedure using sqlplus i can see using set serverout on..

can you please give advise

Tom Kyte
March 22, 2007 - 10:12 am UTC

youch, I hope you really meant:

we have a modular set of routines in a couple of packages, the total number of lines of code is about 7,000.

If you really have a single procedure with 7,000 lines - man, I'd hate to inherit your code.....


dbms_output goes "no where" when run in a job. Dbms_output just puts text into a plsql table (array). The client would retrieve this and print it somewhere. However, the client is the job queue and the job queue cannot read printed text so it just ignores it.


You would have to use utl_file or insert the information into a table - for example:

ops$tkyte%ORA9IR2> create or replace procedure my_job
  2  as
  3  begin
  4          for i in 1 .. 3
  5          loop
  6                  dbms_output.put_line( 'doing the ' || i || ' loop...' );
  7          end loop;
  8  end;
  9  /

Procedure created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec my_job
doing the 1 loop...
doing the 2 loop...
doing the 3 loop...

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create table job_msg( seq number primary key, dt date, txt varchar2(255) ) organization index;

Table created.

ops$tkyte%ORA9IR2> create sequence s;

Sequence created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> declare
  2          l_job_str long := '
  3  declare
  4          l_data  dbms_output.chararr;
  5          l_lines number := 999999999999;
  6  begin
  7          dbms_output.enable;
  8          my_job;
  9          dbms_output.get_lines( l_data, l_lines );
 10          forall i in 1 .. l_lines
 11                  insert into job_msg (seq,dt,txt) values ( s.nextval, sysdate, l_data(i) );
 12  end;';
 13
 14          l_job number;
 15  begin
 16          dbms_job.submit( l_job, l_job_str );
 17          commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> pause

ops$tkyte%ORA9IR2> select * from job_msg order by seq;

       SEQ DT
---------- ---------
TXT
-------------------------------------------------------------------------------
         1 22-MAR-07
doing the 1 loop...

         2 22-MAR-07
doing the 2 loop...

         3 22-MAR-07
doing the 3 loop...


Job Scheduling

cmmurari, March 26, 2007 - 11:21 am UTC

Great Example. Excellent!.

Cheers,


Job Scheduling

Bob, March 27, 2007 - 11:02 am UTC

I am trying to set up a pair of jobs that will execute two Stored Procedures that grant and revoke a users create session privileges respectively, during certain hours.

I have two procedures working and two jobs in the scheduler also working.

When I run the following statement it returns 2 which is expected

Select count(*) from dba_jobs;

But once the two jobs fire at their appropriate time something weird is happening. Oracle is replicating and/or recreating old jobs that were deleted after testing was complete. So In the morning when I run the same statement as above I expect to get back 2 but instead I have 1+ million jobs in the queue for some reason.

I'm at a loss as to what I should do because I keep removing the jobs that are replicating and they just keep coming back. They are flooding my logger and making hundreds of log files.

The delete is below and is being run under the user that owns the jobs that are getting replicated.

declare
cursor myCursor is Select job from all_jobs;
begin

for rec in myCursor loop
DBMS_JOB.remove(rec.job);
COMMIT;
end loop;
end;


Tom Kyte
March 27, 2007 - 12:00 pm UTC

this does not make sense, the act of running a job has never 'brought back dead ones'.

So, these millions of jobs that appear - what are they exactly?

DBMS_SCHEDULER.CREATE_JOB

Arindam Mukherjee, July 07, 2007 - 2:48 am UTC

Sir,

Is it possible to submit five (5) jobs at a time with ONE "DBMS_SCHEDULER.CREATE_JOB" in Oracle 10g?
I have already delved into Oracle documents and google but could not find the syntax. Say, everyday at 12:30 night, I like to execute five (5) procedures e.g. proc_1, proc_2, proc_3, proc_4, proc_5 with ONE "DBMS_SCHEDULER.CREATE_JOB"? "Commit" exists in every procedure. So I assume I do not need any extra commit after scheduler running like dbms_job.

Please help me get the right syntax.

Tom Kyte
July 07, 2007 - 10:50 am UTC

schedule a plsql block that looks like begin p1; p2; p3; ....

Please clarify it

Arindam Mukherjee, July 07, 2007 - 11:25 am UTC

Sir,

I am grateful to you for your quick response. Still I have a doubt.
If I write such a way p1, p2,p3 .. in line with your instruction, are all these running in parallel or these will run serially?
I am sorry for my silly question.

Tom Kyte
July 08, 2007 - 9:21 am UTC

serial, if you want them "in parallel", submit a separate job for each and ensure job queue processes is set high enough for as many as you want to run at the same time to run

how many jobs?

Samuel, January 22, 2008 - 2:25 pm UTC

We are planning to schedule 20000 jobs daily using dbms_jobs that would span 8 hours. This means every hour 20000/8 = 2500 jobs would start to run. We have 12 cpus on the server. Does it mean that only 12 jobs will run at a time even though 2500 jobs were scheduled to run at the same time. Is this correct? Thanks.
Tom Kyte
January 22, 2008 - 6:32 pm UTC

you can set job queue processes up to 1,000 and have 1,000 running concurrently.

Not that you'll actually be able to have 1,000 run concurrently on 12 cpus, but they can all be running at the same time.

But you probably want no more than 24 going on a 12 cpu machine (unless there is other activity of course, then you want less than 24)

How many job?

Samuel, January 22, 2008 - 7:37 pm UTC

Thanks. It is estimated that it would take about 3 minutes for each job to complete. we were wondering if we have enough resource (12 cpus) on the server to support. How do you calculate to find if we need more cpus or not to support 2500 jobs per hour? We are planning to stagger the jobs in such a way that for example: 400 jobs at 10am, 400 jobs at 10:10, 400 at 10:20 and so on. Thanks for your advice
Tom Kyte
January 22, 2008 - 7:56 pm UTC

well, if they need 3 cpu MINUTES, you are in a world of hurt

ops$tkyte%ORA10GR2> select 2500*3, 12*60 from dual;

    2500*3      12*60
---------- ----------
      7500        720



In one hour to run 2500 things that need 3 cpu minutes, you need 7,500 cpu minutes.

you have 12cpus * 60 cpu/minutes per cpu = 720.

Even if they only need 1.5 cpu minutes (50% IO, 50% cpu), you cannot get there from here. Math prevents it.

How many cpus?

Samuel, January 22, 2008 - 10:27 pm UTC

Thanks for your response. Looks like it is unrealistic what we are trying to do. By the way we have two-node RAC (each has 6 cpus). Do you think we should add more servers/cpus in our RAC to achieve 2500 jobs per hour? Thanks.
Tom Kyte
January 23, 2008 - 7:48 am UTC

did you do the math?

ops$tkyte%ORA10GR2> select 2500*3, 12*60 from dual;

    2500*3      12*60
---------- ----------
      7500        720



you would need like 150 cpu's - I'd be rethinking "a job that takes 3 minutes", I'd be spending time figuring out how to get that to be 3 seconds instead. That might be more manageable.

Concurrency of jobs

Wiktor Moskwa, January 23, 2008 - 8:10 am UTC

Samuel:
...By the way we have
two-node RAC (each has 6 cpus). Do you think we should add more servers/cpus in our RAC to achieve
2500 jobs per hour?...

You should take concurrency of your jobs into consideration too. Remember that running 12 jobs (each needs 3 minutes of CPU time) on 12 CPU machine can take from 3 minutes (ideally concurrent) to 36 minutes (serialized) of elapsed time.

Maybe the design decision of putting some things into 2500 CPU-intensive jobs/hour was unrealistic and the problem can be solved simpler but I'm only guessing here.

Regards,
Wiktor Moskwa

A reader, May 12, 2008 - 2:19 pm UTC

Hi tom

I can manage to run the job everyday except sunday and every two hours within these days, but I also want it to be run only once on Sunday.How can I add this to code ??

Here is my code:


BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYS"."TK"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
DECLARE XX NUMBER;BEGIN SELECT 1 INTO XX FROM DUAL;END;
end;',
repeat_interval => 'FREQ=HOURLY;INTERVAL=2;BYDAY=MON,TUE,WED,THU,FRI, SAT;',
start_date => systimestamp,
job_class => 'DEFAULT_JOB_CLASS',
auto_drop => FALSE,
enabled => TRUE);
END;
/

Completion time of job

Bhavesh Ghodasara, May 29, 2008 - 12:41 am UTC

Hi Tom,

How can i get completion time of job?

Because i scheduled a job at 4 am. based on that job i scheduled report at 9am. But if job not completes till 9 am report will not run.(i.e. report is vb exe which sends mail to user with data, i scheduled it using windows scheduled tasks) And at 9:30 when i checked user_jobs, it shows job was completed. So, I need to know what is exact completion time of job. and based on that information I want to reschedule time of report.

Thanks in advance.
Tom Kyte
May 29, 2008 - 7:52 am UTC

it would depend on what you are using to run the job of course - and that you don't actually.... well... mention.


if it is dbms_job - you would query dba_jobs_running to see if it was still running.

the scheduler package has a comprehensive (documented) set of views to go with it as well.

Bhavesh Ghodasara, May 29, 2008 - 8:04 am UTC

Hi,

Please find the script of my job.
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'HSS_DR_daily(to_char(trunc(sysdate)-1, ''dd-Mon-yyyy'') ||'' 00:00:00'',to_char(trunc(sysdate)-1, ''dd-Mon-yyyy'') ||'' 23:59:59'');
'
,next_date => to_date('30/05/2008 04:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1)+4/24 '
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;

I am using user_jobs to see whether it is running or not? If this_date is null, it means running else not. But my question is May i get exact time when it was completed?
I know job is completed between 9am to 9:30 am. but I want to know the exact time.
I already read some document and also search your site before posting problem.

Thanks for reply.

Tom Kyte
May 29, 2008 - 8:17 am UTC

you would have to record that yourself. the DBMS_JOB package does not do that. The scheduler (new in 10g) can keep a history, but dbms_job does not.

(wow, dates as strings :( hss_dr_daily is poorly spec'ed out.)


'hss_dr_daily( ... ); insert into your_history( ''hss_dr_daily'', sysdate );'


maintain your own history if you need one (or use the scheduler package instead)

dbms_job schedule

Megala, July 10, 2008 - 7:39 pm UTC

Tom:

I need to refresh materialized view jobs (remote data replication) once a month on 10th day of each month at 1 AM EST time.

Database : 10.2.0.1
Database server runs in UTC time.

DBMS_JOB.SUBMIT job code is :

dbms_job.submit(:n,'dbms_mview.refresh(''"IES1"."THIES_MASTER_HEADER"'', ''C'', atomic_refresh => FALSE);',TO_DATE('10-JUL-08 05:00:00','DD-MON-YY HH24:MI:SS'),'TRUNC(LAST_DAY(SYSDATE))+10+(5/24) /* Refresh 10th day of each month at 1AM EST */');


Does it look ok as far as interval parameter setting is concerned ?

NOTE:
=====
I am using dbms_mview.refresh to set atomic_Refresh => FALSE to behave the MVIEW refresh the old way of truncate/insert /*+ append */ as in 9i.


Thanks!

Tom Kyte
July 11, 2008 - 8:09 am UTC

as long as the job runs that month, sure.

It'll take the date the job was last run
get to the last day of that month
truncate off the time bit
add 10 days
add 5 hours

if the job runs on the 10th at 5am local time, it'll work perfectly. If it runs anytime before the end of that month, it'll work perfectly.

Thank you

Megala, July 11, 2008 - 11:17 am UTC


UTC time equivalent to 5 AM EDT in dbms_job schedule

Megala, July 24, 2008 - 10:06 am UTC

Tom:

Oracle version : 10.2.0.1
Database server runs in UTC timezone.

I need to set up an oracle job to run at 5 AM EDT (should be run according to Day light Savings Time).

How do i set up the 5 AM EDT in UTC time ?
I understand UTC is not time daylight Savings Time aware.

Thanks Tom for any suggestions.



Schedule a job

Sikki, September 02, 2008 - 6:16 am UTC

Hi Tom,

How do i schedule a job to run on 1st date of every month.?

Thanks in Advance,
Sikki

Tom Kyte
September 02, 2008 - 11:47 am UTC

you cannot figure out how to get the first day of the next month?

ops$tkyte%ORA10GR2> select sysdate, trunc(add_months(sysdate,1),'mm') from dual;
SYSDATE   TRUNC(ADD
--------- ---------
02-SEP-08 01-OCT-08


Conflicting jobs

Bonnie, September 02, 2008 - 11:30 am UTC

Hi Tom,

I have a job I run every wednesday. Recently, I've been asked to also include another run on the 2nd of each month. My problem is, the 2nd sometimes coincide with the Wednesday runs. How can stop one job from runing if they happen to coincide on the same date? I'm afraid if I leave it as is, the job could lock each other out. The process takes about 2 hrs to complete, and the timing is critical, so I can't afford to have it locking each other out.

The next date values for the two jobs are as follows:

TRUNC(ADD_MONTHS(SYSDATE,1),'MON') + 1+5/24

next_day(trunc(sysdate),'WEDNESDAY')+5/24

Any help is greatly appreciated.

Thank you.
Bonnie
Tom Kyte
September 02, 2008 - 1:08 pm UTC

 case when to_char(sysdate,'dd')='01' then trunc(sysdate,'dd')+1
      else least( next_day(trunc(sysdate),'WED'), 
                  add_months(trunc(sysdate,'mm'),1)+1)
        end + 5/24



seems you want a single job...

on the first of the month, you know - you want to run tomorrow.
every other day you run, you know you want the least of
a) next wednesday
b) the 2nd of the next month...


db job interval

A reader, March 03, 2009 - 6:30 pm UTC

Hi Tom,

How can execute a procedure at 6:00 A.M. And 6:00 P.M. every day.

Thanks
Tom Kyte
March 03, 2009 - 9:36 pm UTC

you want a function that evaluates to the next 600 or 1800 hours. If the current time is between 0000 and 0500 - then 0600 on the current date. If the time is between 0600 and 1700 - then 1800 on the current date. Else, 0600 tomorrow:


ops$tkyte%ORA10GR2> select x, case
  2  when to_number(to_char(x,'hh24')) between 6 and 17 then trunc(x)+18/24
  3  when to_number(to_char(x,'hh24')) between 0 and 5 then trunc(x)+6/24
  4  else trunc(x+1)+6/24
  5  end
  6  from (select sysdate + level/24 x from dual connect by level <= 24 )
  7  /

X                    CASEWHENTO_NUMBER(TO
-------------------- --------------------
03-mar-2009 22:29:35 04-mar-2009 06:00:00
03-mar-2009 23:29:35 04-mar-2009 06:00:00
04-mar-2009 00:29:35 04-mar-2009 06:00:00
04-mar-2009 01:29:35 04-mar-2009 06:00:00
04-mar-2009 02:29:35 04-mar-2009 06:00:00
04-mar-2009 03:29:35 04-mar-2009 06:00:00
04-mar-2009 04:29:35 04-mar-2009 06:00:00
04-mar-2009 05:29:35 04-mar-2009 06:00:00
04-mar-2009 06:29:35 04-mar-2009 18:00:00
04-mar-2009 07:29:35 04-mar-2009 18:00:00
04-mar-2009 08:29:35 04-mar-2009 18:00:00
04-mar-2009 09:29:35 04-mar-2009 18:00:00
04-mar-2009 10:29:35 04-mar-2009 18:00:00
04-mar-2009 11:29:35 04-mar-2009 18:00:00
04-mar-2009 12:29:35 04-mar-2009 18:00:00
04-mar-2009 13:29:35 04-mar-2009 18:00:00
04-mar-2009 14:29:35 04-mar-2009 18:00:00
04-mar-2009 15:29:35 04-mar-2009 18:00:00
04-mar-2009 16:29:35 04-mar-2009 18:00:00
04-mar-2009 17:29:35 04-mar-2009 18:00:00
04-mar-2009 18:29:35 05-mar-2009 06:00:00
04-mar-2009 19:29:35 05-mar-2009 06:00:00
04-mar-2009 20:29:35 05-mar-2009 06:00:00
04-mar-2009 21:29:35 05-mar-2009 06:00:00

24 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable n number
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_job.submit( :n, what=> 'null;', interval => 'case when to_number(to_char(sysdate,''hh24'')) between 6 and 17 then trunc(sysdate)+18/24 when to_number(to_char(sysdate,''hh24'')) between 0 and 5 then trunc(sysdate)+6/24 else trunc(sysdate+1)+6/24 end' );

PL/SQL procedure successfully completed.


jobs

David, April 25, 2009 - 11:43 pm UTC

Whe job queue processes were restarted, there are 3 jobs that all started and running within the same minute on the 30 minute cycle. We wanted to stagger them in such a way that the jobs run in 10 minute gap between the jobs. What is the best way to do? As the jobs are already running in production environment, what is the clean way to change the next start time of these jobs? drop and recreate them?
Thanks

Tom Kyte
April 27, 2009 - 1:52 pm UTC

use the scheduler and job chains would be the first approach - else you are always going to have slippage (jobs do NOT start precisely when they are scheduled to start).

Or have job 1 schedule job 2 when it is done, job 2 schedule 3, and 3 schedule 1.

create a job, with create_job for specific user

Randhir, May 20, 2009 - 4:16 pm UTC

Hello Tom,
Is there a way in DBMS_scheduler to schedule a UNIX based job that runs with a specific UNIX OS user versus nobody user.

Intention is if xyz.sh script runs, output files from the (xyz.sh) script should be owned by requested OS user.

Assumptions would be xyz.sh will have execute permissions for the requested user.

Oracle version# Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

sample script
> cat test.sh
#!/bin/ksh
echo "hello world" > /usr/spool/data/test.txt

Thanks
Randhir

job email

sam, August 25, 2009 - 7:38 pm UTC

Tom:

I would like to set something that sends me email alert when a job fails. What would be the best thign to do it.
I use oracle 9i and dbms_job. I think 11g and dbms_shcedule have that feature already.

I was thinking of setting an after update trigger on USER_JOBS and send an email whenever FAILURES is updated


Tom Kyte
August 25, 2009 - 8:46 pm UTC

instead of scheduling:

begin p; end;


schedule:

begin
  p;
exception
when others then
     your_email_procedure( send whatever );
     RAISE;
end;


You cannot put an after update trigger on a view (user_jobs) and you cannot put a trigger on sys tables (which the view are based on)

How to find out a job has finished successfully

Jakub Illner, August 26, 2009 - 9:29 am UTC

Dear Tom,

I am trying to find the best way how to identify when a DBMS_SCHEDULER job has finished and whether it succeeded or not. The trouble is how to identify an appropriate job execution.

Why we need to know this - we use jobs to implement diy paralelism. We start N jobs in parallel, each job working on its own partition of data. After all the jobs finish successfully, we continue with the next processing step. The issue is to find out that all the jobs *finished* and that they finished *successfully*.

At this moment we use the method, which works but I am afraid it is not 100% correct. Working example is shown below. The reason why I think this is not perfect is that to check a job has finished we look at USER_SCHEDULER_RUNNING_JOBS, but there might be delay before a job is enabled AND row is in USER_SCHEDULER_RUNNING_JOBS and if we check too early, we can finish the procedure before the jobs actually started.

Also to check whether a job has finished successfully we look at USER_SCHEDULER_JOB_LOG; but for a single job there might be many records in USER_SCHEDULER_JOB_LOG and it is not clear how to find entry corresponding to our execution. We use timestamp currently but this is not foolproof.

It would help if execution of a job is associated with a unique ID, which would be returned from the RUN_JOB procedure. Unfortunately this is not the case.

I would appreciate if you could suggest better way how to approach this.


SQL> 
SQL> set echo on
SQL> set timing on
SQL> set serverout on
SQL> 
SQL> declare
  2    v_start_timestamp timestamp;
  3    v_job_action varchar2(4000) := 'begin dbms_lock.sleep(60); end;';
  4    v_parallel_jobs integer := 4;
  5    v_sleep_seconds integer := 10;
  6    v_jobs_running integer;
  7    v_is_running integer;
  8    v_is_succeeded integer;
  9    type t_job_list is table of varchar2(30) index by binary_integer;
 10    v_job_list t_job_list;
 11  begin
 12    /* -------------------------------------*/
 13    /*  Run jobs */
 14    /* -------------------------------------*/
 15    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss')||' running jobs');
 16    v_start_timestamp := sysdate;
 17    for i in 1..v_parallel_jobs loop
 18    v_job_list(i) := 'MYJOB_'||i;
 19    dbms_scheduler.create_job(
 20      job_name => v_job_list(i)
 21    , job_type => 'PLSQL_BLOCK'
 22    , job_action => v_job_action
 23    , enabled => false
 24    , auto_drop => true
 25    );
 26    dbms_scheduler.set_attribute (
 27      name => v_job_list(i)
 28    , attribute => 'instance_stickiness'
 29    , value => false
 30    );
 31    dbms_scheduler.enable (
 32      name => v_job_list(i)
 33    );
 34    end loop;
 35    /* -------------------------------------*/
 36    /* Wait for job completion */
 37    /* -------------------------------------*/
 38    loop
 39    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss')||' waiting '||v_sleep_seconds||' sec before check');
 40    dbms_lock.sleep(v_sleep_seconds);
 41    v_jobs_running := 0;
 42    for i in v_job_list.first .. v_job_list.last loop
 43      select count(*)
 44      into v_is_running
 45      from user_scheduler_running_jobs
 46      where job_name = v_job_list(i);
 47      if (v_is_running > 0) then
 48        v_jobs_running := v_jobs_running+1;
 49      end if;
 50    end loop;
 51    exit when v_jobs_running <= 0;
 52    end loop;
 53    /* -------------------------------------*/
 54    /* Check all jobs finished correctly */
 55    /* -------------------------------------*/
 56    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss')||' checking all jobs finished ok');
 57    for i in v_job_list.first .. v_job_list.last loop
 58    select sum(case when status = 'SUCCEEDED' then 1 else null end) as is_succeeded
 59    into v_is_succeeded
 60    from user_scheduler_job_log
 61    where job_name = v_job_list(i)
 62    and log_date >= v_start_timestamp;
 63    if (v_is_succeeded <= 0) then
 64      raise_application_error(-20100,'Job '||v_job_list(i)||' did NOT finish with status SUCCEEDED');
 65    end if;
 66    end loop;
 67    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss')||' procedure completed');
 68  end;
 69  /
14:41:20 running jobs                                                           
14:41:20 waiting 10 sec before check
14:41:30 waiting 10 sec before check
14:41:40 waiting 10 sec before check
14:41:50 waiting 10 sec before check
14:42:00 waiting 10 sec before check
14:42:10 waiting 10 sec before check
14:42:20 waiting 10 sec before check
14:42:30 checking all jobs finished ok
14:42:30 procedure completed  

Procedura PL/SQL úsp¿¿n¿ dokon¿ena.

Uplynulo: 00:01:10.26
SQL> 
SQL> spool off


Many thanks,
Jakub

job

A reader, August 26, 2009 - 11:10 am UTC

thanks tom

Does this also mean every job has to be for a PL/SQL program (because of the exception). You probably can add the code in the job itself but it would nto be very neat. How do you recommend it usually?

schedule:


begin
p;
send_email (job success);

exception
when others then
send_email (job failed );
RAISE;
end;


Tom Kyte
August 26, 2009 - 7:31 pm UTC

... but it would nto be
very neat.....

Why isn't is neat? It looks absolutely beautiful to me, I love it.


If you have a job that needs to send a notification when it fails with dbms_job (requirement)

then you shall submit the above block (solution, elegant, short, neat, clear, concise)

use scheduler attribute: raise_events

Pasko, August 27, 2009 - 9:24 am UTC

Hi Jakub,

Another way to get informed about the Job status is to set the Scheduler Attribute 'raise_events' and specify the events that you need.
After the Job completes, the Scheduler will enqueue these Events in the internal Queue: SYS.SCHEDULER$_EVENT_QUEUE, which you could later dequeue and check their status.

I have modified your pl-sql Block from above and tested this as follows:

create or replace procedure parallel_jobs_notify
as
    v_start_timestamp timestamp;
    v_job_action varchar2(4000) := 'begin dbms_lock.sleep(60); end;';
    v_parallel_jobs integer := 4;
    v_sleep_seconds integer := 10;
    v_jobs_running integer;
    v_is_running integer;
    v_is_succeeded integer;
    type t_job_list is table of varchar2(30) index by binary_integer;
    v_job_list t_job_list;
  begin
    /* -------------------------------------*/
    /*  Run jobs */
    /* -------------------------------------*/
    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss')||' running jobs');
    v_start_timestamp := sysdate;
    for i in 1..v_parallel_jobs loop
       v_job_list(i) := 'MYJOB_'||i;
       dbms_scheduler.create_job(
         job_name => v_job_list(i)
       , job_type => 'PLSQL_BLOCK'
       , job_action => v_job_action
       , enabled => false
       , auto_drop => true
       );
       dbms_scheduler.set_attribute (
         name => v_job_list(i)
       , attribute => 'instance_stickiness'
       , value => false
       );
       /* added by pasko */
       dbms_scheduler.set_attribute(name      => v_job_list(i),
                                   attribute => 'raise_events',
                                   value     => dbms_scheduler.job_succeeded +
                                                dbms_scheduler.job_failed +
                                                dbms_scheduler.job_broken +
                                                dbms_scheduler.job_completed +
                                                dbms_scheduler.job_stopped);
       /*end added by pasko */
       dbms_scheduler.enable (
         name => v_job_list(i)
       );
    end loop;
    /* -------------------------------------*/
    /* Wait for job completion */
    /* -------------------------------------*/
    /*
    loop
       dbms_output.put_line(to_char(sysdate,'hh24:mi:ss')||' waiting '||v_sleep_seconds||' sec before check');
       dbms_lock.sleep(v_sleep_seconds);
       v_jobs_running := 0;
       for i in v_job_list.first .. v_job_list.last loop
         select count(*)
         into v_is_running
         from user_scheduler_running_jobs
         where job_name = v_job_list(i);
         if (v_is_running > 0) then
           v_jobs_running := v_jobs_running+1;
         end if;
       end loop;
       exit when v_jobs_running <= 0;
    end loop;
    */
    /* -------------------------------------*/
    /* Check all jobs finished correctly */
    /* -------------------------------------*/
    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss')||' checking all jobs finished ok');
    for i in v_job_list.first .. v_job_list.last loop
       /*
       select sum(case when status = 'SUCCEEDED' then 1 else null end) as is_succeeded
       into v_is_succeeded
       from user_scheduler_job_log
       where job_name = v_job_list(i)
       and log_date >= v_start_timestamp;
       if (v_is_succeeded <= 0) then
         raise_application_error(-20100,'Job '||v_job_list(i)||' did NOT finish with status SUCCEEDED');
       end if;
       */
       
       /* added by pasko */
       /* check job status by dequeueing messages from the Queue: SYS.SCHEDULER$_EVENT_QUEUE
          Events raised by the Scheduler expire in 24 hours by default.
          You can change this expiry time by setting the event_expiry_time- 
          Scheduler attribute with the SET_SCHEDULER_ATTRIBUTE 
    begin
             dbms_scheduler.set_scheduler_attribute(attribute => 'event_expiry_time',value => 5); 
          end;
          Required Grants>>>
          connect as SYS
    ---------------
          GRANT EXECUTE ON SYS.DBMS_AQ to pascal;
          GRANT EXECUTE ON SYS.DBMS_AQADM to pascal;
    ---------------
          connect as <user> who will be checking job-status
          exec DBMS_SCHEDULER.add_event_queue_subscriber;
    ---------------
          -->you can even check enqueued messages per SQL e.g
          select * from sys.SCHEDULER$_EVENT_QTAB se
   where se.user_data.object_name = 'MYJOB_1'
       */
       DECLARE
          l_dequeue_options    DBMS_AQ.dequeue_options_t;
          l_message_properties DBMS_AQ.message_properties_t;
          l_message_handle     RAW(16);
          l_queue_msg          sys.scheduler$_event_info;
        BEGIN
          l_dequeue_options.consumer_name := 'PASCAL';

          DBMS_AQ.dequeue(queue_name => 'SYS.SCHEDULER$_EVENT_QUEUE',
                          dequeue_options => l_dequeue_options,
                          message_properties => l_message_properties,
                          payload => l_queue_msg,
                          msgid => l_message_handle);
          COMMIT;
          dbms_output.put_line('Job-Name =>'||v_job_list(i) );
          dbms_output.put_line('---------------------------');
          DBMS_OUTPUT.put_line ('event_type : ' || l_queue_msg.event_type);
          DBMS_OUTPUT.put_line ('object_owner : ' || l_queue_msg.object_owner);
          DBMS_OUTPUT.put_line ('object_name : ' || l_queue_msg.object_name);
          DBMS_OUTPUT.put_line ('event_timestamp: ' || l_queue_msg.event_timestamp);
          DBMS_OUTPUT.put_line ('error_code : ' || l_queue_msg.error_code);
          DBMS_OUTPUT.put_line ('event_status : ' || l_queue_msg.event_status);
          DBMS_OUTPUT.put_line ('log_id : ' || l_queue_msg.log_id);
          DBMS_OUTPUT.put_line ('run_count : ' || l_queue_msg.run_count);
          DBMS_OUTPUT.put_line ('failure_count : ' || l_queue_msg.failure_count);
          DBMS_OUTPUT.put_line ('retry_count : ' || l_queue_msg.retry_count);
          dbms_output.put_line('---------------------------');
       END;
    end loop;
       
    dbms_output.put_line(to_char(sysdate,'hh24:mi:ss')||' procedure completed');
end parallel_jobs_notify;
/


SQL> exec parallel_jobs_notify;

11:54:06 running jobs
11:54:06 checking all jobs finished ok
Job-Name =>MYJOB_1
---------------------------
event_type : JOB_SUCCEEDED
object_owner : PASCAL
object_name : MYJOB_1
event_timestamp: 27.08.09 11:55:06,433720 +02:00
error_code : 0
event_status : 0
log_id : 47238
run_count : 1
failure_count : 0
retry_count : 0
---------------------------
Job-Name =>MYJOB_2
---------------------------
event_type : JOB_SUCCEEDED
object_owner : PASCAL
object_name : MYJOB_3
event_timestamp: 27.08.09 11:55:06,587127 +02:00
error_code : 0
event_status : 0
log_id : 47239
run_count : 1
failure_count : 0
retry_count : 0
---------------------------
Job-Name =>MYJOB_3
---------------------------
event_type : JOB_SUCCEEDED
object_owner : PASCAL
object_name : MYJOB_4
event_timestamp: 27.08.09 11:55:06,627372 +02:00
error_code : 0
event_status : 0
log_id : 47240
run_count : 1
failure_count : 0
retry_count : 0
---------------------------
Job-Name =>MYJOB_4
---------------------------
event_type : JOB_SUCCEEDED
object_owner : PASCAL
object_name : MYJOB_2
event_timestamp: 27.08.09 11:55:06,669587 +02:00
error_code : 0
event_status : 0
log_id : 47241
run_count : 1
failure_count : 0
retry_count : 0
---------------------------
11:55:06 procedure completed

PL/SQL procedure successfully completed.

SQL>

Tom Kyte
August 28, 2009 - 4:49 pm UTC

if the goal is

... Why we need to know this - we use jobs to implement diy paralelism. We start N jobs in parallel, each job working on its own partition of data. After all the jobs finish successfully, we continue with the next processing step. The issue is to find out that all the jobs *finished* and that they finished *successfully*. ...

I believe job chains is the simple correct answer.

dbms_scheduler

dulu, October 30, 2009 - 4:44 am UTC

If you run the following block:

set serveroutput on
declare

v1 timestamp with time zone;

begin

v1:= null;
dbms_scheduler.evaluate_calendar_string( calendar_string => 'FREQ=MONTHLY; INTERVAL=1'
, start_date => TIMESTAMP '2009-10-31 10:00:00.00'
, return_date_after => TIMESTAMP '2009-10-31 10:00:00.00'
, next_run_date => v1 );

dbms_output.put_line(v1);

end;
/


you get this result

31-DEC-09 10.00.00.000000 +00:00
PL/SQL procedure successfully completed.

Now changing the interval to a 2 you get

set serveroutput on
declare

v1 timestamp with time zone;

begin

v1:= null;
dbms_scheduler.evaluate_calendar_string( calendar_string => 'FREQ=MONTHLY; INTERVAL=2'
, start_date => TIMESTAMP '2009-10-31 10:00:00.00'
, return_date_after => TIMESTAMP '2009-10-31 10:00:00.00'
, next_run_date => v1 );

dbms_output.put_line(v1);

end;
/

you get the same result

31-DEC-09 10.00.00.000000 +00:00
PL/SQL procedure successfully completed.

So adding one month using INTERVAL=1 does not work because november does not have a 31st day. How can I get around this? I DO want it to pick up the last day in november.... is there any calendar string that will force this behaviour instead of just skipping to the next month??

many thanks

JOBS SCHEDULAR IN ORACLE 9I

Nahid Asadi, February 08, 2010 - 4:21 am UTC

hi Tom,
i read all thing you said about job schedular and i wrote that script (declare
l_job number;
begin
dbms_job.submit(l_job,
'nahid;',
trunc(sysdate)+9/24,
'trunc(sysdate)+1+9/24');
end;)
and it sucssessfully run and i saw it in dba_jobs
but it didnt run in next_date
i set job_queue_prosses=10
but i couldnt set job_queue_interval because it is oracle9i
thanks in avvance.
Nahid Asadi
Tom Kyte
February 15, 2010 - 10:04 am UTC

do a bit of debugging for us please.

check your alert log, see if it ran but failed.

post the (formatted so we can READ IT) contents of dba_jobs for that job, that'll show us useful information.

job scheduling concurrency issues

Ravi, March 19, 2010 - 5:54 pm UTC

Hi Tom,

We have an a dashboard application where we need to run ETL's few times in span of 5 minutes. Run time for these ETL's are about 15 secs

We have 3 JVM's which will kick off these jobs(pl/sql stored procedures) every 5 mins. These JVM's run independently of each other.

So there can be overlap of these jobs running from different JVM's(same job could potentially kicked of at the same exact time from any of the JVM's).

The application is designed such a way that there is a status table which will update the status='R'(autonomous transaction) when the job starts. And it updates the status='C' when it finishes. Before running the job the status is checked. If it is 'R' the procedure exits without doing anything.

Now, i see sometimes, the jobs are started at exact same second.

This is causing

1) sometimes deadlock issues
2) sometimes overwriting each other
3) sometimes duplicate data in the tables

this is the legacy application i have inherited. Is there a better way of doing this?

Your inputs are greatly appreciated.

Thanks!
Tom Kyte
March 20, 2010 - 9:21 am UTC

why wouldn't you just use the job scheduler with job chains. To write an application for this sounds like a waste of time.

With the scheduler, you get your job chains (do not run X until A,B,C complete etc), you get logging automagically (no code), you get calendars (run on this 'schedule' where schedule is about as complex as you want to get), and you get the fact that the jobs will only attempt to run when the database is up - and you don't have anything extra to monitor outside of the database.


I would start by erasing all code and teaching the developers about the feature set of the database itself. It is fairly obvious (a legacy application with a slew of bugs!!?!?!?! how can you have legacy applications that cause 1,2,3 - why would not they have been fixed years ago??) that the existing code doesn't work.



job scheduling concurrency issues

Ravi, March 22, 2010 - 11:49 am UTC

Thanks Tom.

You are absolutely right. the whole application is a mess. I am not sure how it ran for all these years. Yes, they have a job scheduler in this company. I might have to convince them to use it. I have lot of cleaning up to do.

Thanks!
Tom Kyte
March 22, 2010 - 12:47 pm UTC

... I am not sure how it
ran for all these years. ...

you told us above that it *did not* run all of those years.


This is causing

1) sometimes deadlock issues
2) sometimes overwriting each other
3) sometimes duplicate data in the tables


it is not like it was working and something changed and it broke - it never worked.


I know they have a scheduler, it comes with the database...

job scheduling concurrency issues

Ravi, March 22, 2010 - 3:57 pm UTC

Well, what i meant was, the process is broken but it continued to run for years with existing issues. This is just one issue, there are bunch of other issues that i have to fix or rewrite. This client is still on 9i hence no DBMS_SCHEDULER. They have 3rd party scheduling tool which could be used for defining job dependencies etc.

schedule a job as a different owner,

A reader, March 24, 2010 - 2:39 pm UTC

I need to schedule a job that analyzes table owned by owner 'ABC'. However, I don't have the password of 'ABC' to login as. Using my account (which has dba role), how can I create a job that is owned by 'ABC'?

This is the script that I am planning to use:

begin
dbms_scheduler.create_job(
job_name => 'ANALYZE TABLE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin DBMS_STATS.GATHER_TABLE_STATS('ABC','ORIG_COORDINATE', NULL,NULL, FALSE, NULL, 4, 'DEFAULT',TRUE); end; '
,start_date => '03/25/2010 02:00 AM'
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'New Job');
end;
/

How should I make this job owned by 'ABC'?

Thanks,


to: a reader (job as different owner)

glw, March 25, 2010 - 10:22 am UTC

Try:
begin
dbms_scheduler.create_job(
job_name => 'ABC.ANALYZE TABLE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin DBMS_STATS.GATHER_TABLE_STATS('ABC','ORIG_COORDINATE', NULL,NULL, FALSE,
NULL, 4, 'DEFAULT',TRUE); end; '
,start_date => '03/25/2010 02:00 AM'
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'New Job');
end;

Just add the schema name in the job name

DBA_job

Gautham, June 28, 2010 - 8:22 am UTC

Hi Tom,

I have a dba job which is refreshing a mview at regular intervals of time for example(for every 2hrs). I had issued the following script for that.

"mview name" Start with sysdate next trunc(SYSDATE)+1+1/12.

Now my application demands me to keep the refresh timings daily at 3am,10am and 7pm.

Please help me with this.
Tom Kyte
July 06, 2010 - 11:37 am UTC

easiest way that comes immediately to mind (short of using the dbms_scheduler package to schedule the refresh call to dbms_mview.refresh) would be to create a function:

ops$tkyte%ORA11GR2> create or replace function my_next_date( p_date in date ) return date
  2  as
  3  begin
  4          return
  5          case when to_char(p_date,'hh24') between '00' and '02' then trunc(p_date)+3/24
  6              when to_char(p_date,'hh24') between '03' and '09' then trunc(p_date)+10/24
  7              when to_char(p_date,'hh24') between '10' and '18' then trunc(p_date)+19/24
  8                          else trunc(p_date)+1+3/24
  9          end;
 10  end;
 11  /

Function created.

ops$tkyte%ORA11GR2> select the_date,
  2          case when to_char(the_date,'hh24') between '00' and '02' then trunc(the_date)+3/24
  3              when to_char(the_date,'hh24') between '03' and '09' then trunc(the_date)+10/24
  4              when to_char(the_date,'hh24') between '10' and '18' then trunc(the_date)+19/24
  5                          else trunc(the_date)+1+3/24
  6          end
  7   from (select to_date( '01-jan-2010 ' || to_char(level-1, '00'), 'dd-mon-yyyy hh24' ) the_date from dual connect by level <= 24
  8         union all
  9         select to_date( '01-jan-2010 ' || to_char(level-1, '00'), 'dd-mon-yyyy hh24' )-1/24/60/60 the_date from dual connect by level <= 24
 10                   union all
 11         select to_date( '01-jan-2010 ' || to_char(level-1, '00'), 'dd-mon-yyyy hh24' )+1/24/60/60 the_date from dual connect by level <= 24 )
 12   order by 1
 13  /

THE_DATE             CASEWHENTO_CHAR(THE_
-------------------- --------------------
31-dec-2009 23:59:59 01-jan-2010 03:00:00
01-jan-2010 00:00:00 01-jan-2010 03:00:00
01-jan-2010 00:00:01 01-jan-2010 03:00:00
01-jan-2010 00:59:59 01-jan-2010 03:00:00
01-jan-2010 01:00:00 01-jan-2010 03:00:00
01-jan-2010 01:00:01 01-jan-2010 03:00:00
01-jan-2010 01:59:59 01-jan-2010 03:00:00
01-jan-2010 02:00:00 01-jan-2010 03:00:00
01-jan-2010 02:00:01 01-jan-2010 03:00:00
01-jan-2010 02:59:59 01-jan-2010 03:00:00
01-jan-2010 03:00:00 01-jan-2010 10:00:00
01-jan-2010 03:00:01 01-jan-2010 10:00:00
01-jan-2010 03:59:59 01-jan-2010 10:00:00
01-jan-2010 04:00:00 01-jan-2010 10:00:00
01-jan-2010 04:00:01 01-jan-2010 10:00:00
01-jan-2010 04:59:59 01-jan-2010 10:00:00
01-jan-2010 05:00:00 01-jan-2010 10:00:00
01-jan-2010 05:00:01 01-jan-2010 10:00:00
01-jan-2010 05:59:59 01-jan-2010 10:00:00
01-jan-2010 06:00:00 01-jan-2010 10:00:00
01-jan-2010 06:00:01 01-jan-2010 10:00:00
01-jan-2010 06:59:59 01-jan-2010 10:00:00
01-jan-2010 07:00:00 01-jan-2010 10:00:00
01-jan-2010 07:00:01 01-jan-2010 10:00:00
01-jan-2010 07:59:59 01-jan-2010 10:00:00
01-jan-2010 08:00:00 01-jan-2010 10:00:00
01-jan-2010 08:00:01 01-jan-2010 10:00:00
01-jan-2010 08:59:59 01-jan-2010 10:00:00
01-jan-2010 09:00:00 01-jan-2010 10:00:00
01-jan-2010 09:00:01 01-jan-2010 10:00:00
01-jan-2010 09:59:59 01-jan-2010 10:00:00
01-jan-2010 10:00:00 01-jan-2010 19:00:00
01-jan-2010 10:00:01 01-jan-2010 19:00:00
01-jan-2010 10:59:59 01-jan-2010 19:00:00
01-jan-2010 11:00:00 01-jan-2010 19:00:00
01-jan-2010 11:00:01 01-jan-2010 19:00:00
01-jan-2010 11:59:59 01-jan-2010 19:00:00
01-jan-2010 12:00:00 01-jan-2010 19:00:00
01-jan-2010 12:00:01 01-jan-2010 19:00:00
01-jan-2010 12:59:59 01-jan-2010 19:00:00
01-jan-2010 13:00:00 01-jan-2010 19:00:00
01-jan-2010 13:00:01 01-jan-2010 19:00:00
01-jan-2010 13:59:59 01-jan-2010 19:00:00
01-jan-2010 14:00:00 01-jan-2010 19:00:00
01-jan-2010 14:00:01 01-jan-2010 19:00:00
01-jan-2010 14:59:59 01-jan-2010 19:00:00
01-jan-2010 15:00:00 01-jan-2010 19:00:00
01-jan-2010 15:00:01 01-jan-2010 19:00:00
01-jan-2010 15:59:59 01-jan-2010 19:00:00
01-jan-2010 16:00:00 01-jan-2010 19:00:00
01-jan-2010 16:00:01 01-jan-2010 19:00:00
01-jan-2010 16:59:59 01-jan-2010 19:00:00
01-jan-2010 17:00:00 01-jan-2010 19:00:00
01-jan-2010 17:00:01 01-jan-2010 19:00:00
01-jan-2010 17:59:59 01-jan-2010 19:00:00
01-jan-2010 18:00:00 01-jan-2010 19:00:00
01-jan-2010 18:00:01 01-jan-2010 19:00:00
01-jan-2010 18:59:59 01-jan-2010 19:00:00
01-jan-2010 19:00:00 02-jan-2010 03:00:00
01-jan-2010 19:00:01 02-jan-2010 03:00:00
01-jan-2010 19:59:59 02-jan-2010 03:00:00
01-jan-2010 20:00:00 02-jan-2010 03:00:00
01-jan-2010 20:00:01 02-jan-2010 03:00:00
01-jan-2010 20:59:59 02-jan-2010 03:00:00
01-jan-2010 21:00:00 02-jan-2010 03:00:00
01-jan-2010 21:00:01 02-jan-2010 03:00:00
01-jan-2010 21:59:59 02-jan-2010 03:00:00
01-jan-2010 22:00:00 02-jan-2010 03:00:00
01-jan-2010 22:00:01 02-jan-2010 03:00:00
01-jan-2010 22:59:59 02-jan-2010 03:00:00
01-jan-2010 23:00:00 02-jan-2010 03:00:00
01-jan-2010 23:00:01 02-jan-2010 03:00:00

72 rows selected.



and use my_next_date(sysdate) as the next.

DBA_JOB

Gautham, June 28, 2010 - 8:35 am UTC

Hi Tom,

I had used a following script in dba jobs to refresh my mview for every 6hrs as

next sysdate+1/4.

But,I noticed that next date refresh timing is changing slightly and realized that it should be replaced with

trunc(sysdate)+1+1/4.

Please help me in modifying this job in 9i without re-creating the mview.

Bottom line is ,How to modify a db job in 9i.

Refresh Timings

Gautham, July 07, 2010 - 4:58 am UTC

Hi Tom,
Thanks for the followup.

I need to start my refresh time from 9PM today and it should repeat for every 6 hrs.

So,The interval should be 9pm,3am,9am,3pm

I has tried using the the following script.

Alter materialized view ABC refresh complete start with trunc(sysdate)+21/24 next trunc(sysdate+1)+6/24.

With the above script i am getting the interval as

6am,12pm,6pm,12am...

PLease help me with this tom.

Thanks
Gautham
Tom Kyte
July 08, 2010 - 11:46 am UTC

you won't get 6am, 12pm, 6pm, 12am with that.


you add 1 to sysdate ( and remember, sysdate is the current time, we were told to start the job ANYTIME WE ARE ABLE AFTER 9pm of the current day - it probably started at 9:02 or something - but not 9:00:00 exactly). So, now you have sysdate+1. Then you trunc it, so you have midnight tomorrow, so you get 6am tomorrow. Now, 6am tomorrow it runs and you get the same thing - 6am tomorrow again.


use the approach I just demonstrated to you above.

if the hour is 0, 1, 2 - you want 3am of the current day for the next, trunc(sysdate)+3/24

if the hour is 3, 4, 5 - you want 6am of the current day
trunc(sysdate)+6/24

and so on. Now, you write that function.

DBMS job still repeating even if the interval paramter is null

Sandra, November 29, 2010 - 10:33 pm UTC

Hi Tom,

I would like to ask about dmbs job for one time execution. From what I know, it's only use the interval parameter which is set to null.

But I encountered this problem : the job always updated the next date parameter.

Could you help me to find the solutions.
Thanks a lot

Sandra
Tom Kyte
November 30, 2010 - 2:47 am UTC

give me a "for example". Show us a cut and paste of a sqlplus session that shows

a) you scheduling the job
b) committing - so the job runs
c) some evidence that the job actually ran successfully (have the job insert a row into a table for example and then query that out)
d) evidence that the job rescheduled itself

then we can help you diagnose what is going on. Quick tip: check your alert log to see if the job isn't just FAILING - if it fails, we'll reschedule it 16 times before marking it as "broken" in the hopes that a subsequent execution will succeed.

DBMS_SCHEDULER to Gather_Stats

Rajeshwaran, Jeyabal, January 04, 2011 - 12:44 am UTC

Tom:

We are using Oracle 10GR2 (10.2.0.4.0) on Solaris. We have some Transaction Tables with each tables having some 1200M records around, with STATS locked in production (user_tab_statistics.STATTYPE_LOCKED = 'ALL' with LAST_ANALYZED as 29-OCT-2009) .

I am planning to Re-gather stats on these tables by unlocking them. And highlighed a New Feature introduced in 10GR2 Restoring Previous Versions of Statistics from Product documentation.

This is useful in case newly collected statistics leads to some sub-optimal execution plans ( hoping it should not ;) ) and the administrator wants to revert to the previous set of statistics

Now the question is

1)Gather_stats job has been failing in production So how would that impact this? Also The job is running outside the window in DBMS_SCHEDULER

2)when a job fails, the job queue facility (dbms_job) also writes the error to the alert log in background dump destination. Does DBMS_SCHEDULER will also produce similar alert log in background dump destination ?

I need your help to answer this Question


Tom Kyte
January 04, 2011 - 7:14 am UTC

1) my car won't start - why not?

We are even - what the heck does "failing" mean? What are the symptoms.

dbms_scheduler will start a job and run it, if it runs for a long time - it'll run for a long time.


2) dbms_scheduler logs everything in the dictionary - better than dbms_job.

DBMS_SCHEDULER at two different times

Matthias, March 03, 2011 - 2:47 am UTC

Hello Tom,

we have a scheduler job, that should be run daily at 08:30 (AM) and 14:00 (PM). I don´t know how to get this to different times in one scheduler job. Is that possible?

Thanks in advance.
Tom Kyte
March 03, 2011 - 7:54 am UTC

http://docs.oracle.com/docs/cd/E11882_01/server.112/e17120/scheduse004.htm#ADMIN10040

it is rather easy actually, they have an example of running something every day at 4, 5 and 6pm right there.

DBMS_SCHEDULER at two different times

Matthias, March 03, 2011 - 8:06 am UTC

Hello Tom,

thanks for the followup, but this does not help, because my problem is that the first time a day the job should start at full hour (8:00) and the second time at 30 minutes after 2 o´clock

So i cant use e.g.
FREQ=DAILY;BYHOUR=8,14;BYMINUTE=0,30

because then i have 4 runs a day at 08:00, 08:30, 14:00, 14:30

but i only need 08:00 and 14:30

Thanks!
Tom Kyte
March 03, 2011 - 9:11 am UTC

case when to_char(sysdate,'hh24mi') < '1430'
            then trunc(sysdate) + 14/24 + 30/24/60
            else trunc(sysdate+1)+8/24
        end


since the repeat interval can be a plsql expression.... You can do whatever you like there.

http://docs.oracle.com/docs/cd/E11882_01/server.112/e17120/scheduse004.htm#ADMIN12418

A reader, March 03, 2011 - 1:23 pm UTC

it should be like

case when to_char(sysdate,'hh24mi') < '1430' then
trunc(sysdate+1)+8/24
else
trunc(sysdate) + 14/24 + 30/24/60
end


Thanks


Tom Kyte
March 03, 2011 - 1:41 pm UTC

no, it shouldn't.

They want 8am and 2:30pm (14:30)


Actually, it should be:

case when to_date(sysdate,'hh24mi') < '0800' then
trunc(sysdate)+8/24
when to_date(sysdate,'hh24mi') < '1430' then
trunc(sysdate) + 14/24 + 30/24/60
else trunc(sysdate+1) + 8/24
end;


The first case is for any job running between midnight and 8am. We have to assume that was the last job that was scheduled to run at 2:30pm the day before. It must not have run due to database downtime, or job failure. It is running now, so the next job is at 8am of the current day.

The second case is for any job running between 8am and 2:29pm. That job instance would have to be the 8am job of the current day. The next time it should run would be 2:30pm of the current day. Why didn't it run at 8am? Who cares - it didn't that is all we need to know - and we know that after the 8am job - we need the 2:30pm job.


The else case is for the job running anytime after 2:30pm, we know the next time it should run would be tomorrow at 8am.

A reader, April 28, 2011 - 11:50 pm UTC

Is there a way I can run a job that is a pure SQL file (no PLSQL in it)?

i.e. create a job in PLSQL and give it a SQL file input to run in the background.
Tom Kyte
April 29, 2011 - 8:30 am UTC

you cannot create a job with a "sql file". The job queue (dbms_job) does "PLSQL" only.

You would have to give as input:


begin
any DML statement you want;
end;


If you wanted to do a "file", you would have to write a stored procedure that would take as input the name of the file - then using UTL_FILE - you would open it, read it, and dynamically execute the SQL statement it found within. But I would not suggest this - too many moving bits.

Instead of putting the SQL in a file, put the SQL in the job queue in the first place.

dbms_scheduler.evaluate_calendar_string

Bob, October 19, 2011 - 11:05 am UTC

Can you please tell me the solution, to not skip the month if the date fall under invalid dates (31-FEB-2011)

Very similar question asked by someone.

---------------------------
If you run the following block:

set serveroutput on
declare

v1 timestamp with time zone;

begin

v1:= null;
dbms_scheduler.evaluate_calendar_string( calendar_string => 'FREQ=MONTHLY; INTERVAL=1'
, start_date => TIMESTAMP '2009-10-31
10:00:00.00'
, return_date_after => TIMESTAMP '2009-10-31
10:00:00.00'
, next_run_date => v1 );

dbms_output.put_line(v1);

end;
/

you get this result

31-DEC-09 10.00.00.000000 +00:00
PL/SQL procedure successfully completed.

Now changing the interval to a 2 you get

set serveroutput on
declare

v1 timestamp with time zone;

begin

v1:= null;
dbms_scheduler.evaluate_calendar_string( calendar_string => 'FREQ=MONTHLY; INTERVAL=2'
, start_date => TIMESTAMP '2009-10-31
10:00:00.00'
, return_date_after => TIMESTAMP '2009-10-31
10:00:00.00'
, next_run_date => v1 );

dbms_output.put_line(v1);

end;
/

you get the same result

31-DEC-09 10.00.00.000000 +00:00
PL/SQL procedure successfully completed.

So adding one month using INTERVAL=1 does not work because november does not have a 31st day. How
can I get around this? I DO want it to pick up the last day in november.... is there any calendar
string that will force this behaviour instead of just skipping to the next month??

many thanks
dulu
-----------------------------------------------------------

Dynamic Interval

Ahmed Refa'at, September 08, 2012 - 7:19 am UTC

Hi Tom,

I'm using BDMS_JOB to run a procedure in dynamic inerval, in which the next run time is selected from table that can be updated by the end user

CREATE OR REPLACE PACKAGE PROCCESSING_PKG IS

FUNCTION GET_NEXT_RUN (P$SYSDATE DATE) RETURN DATE;

PROCEDURE PROCCESSING_PROCEDURE (P$CHK_TIME DATE);

END;
..
..
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'BEGIN PROCCESSING_PKG.PROCCESSING_PROCEDURE(SYSDATE); END;'
,next_date => to_date('13:30','hh24:mi')
,interval => ' PROCCESSING_PKG.GET_NEXT_RUN(SYSDATE) '
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;

And everything is working fine, but the DBA asked me to convert to DBMS_SCHEDULER instead of DBMS_JOB, but I couldn't find any help to implement the dynamic calenar in DBMS_SCHEDULER.

So please help me to do this. Its very much appreciated


Dynamic Interval - NO ANSWER

Ahmad Refa'at, October 13, 2012 - 6:48 am UTC

Hi Tom
Please reply my question
Tom Kyte
October 13, 2012 - 8:58 am UTC

you'd probably have to turn your table into a schedule (like a list of dates) and maintain the dates you wanted in a schedule instead of a table.

or, like us, you could just opt to keep using dbms_job.

ops$tkyte%ORA11GR2> select * from user_jobs;

no rows selected

ops$tkyte%ORA11GR2> create materialized view mv
  2  refresh complete
  3  start with sysdate
  4  next sysdate+1
  5  as
  6  select * from emp;

Materialized view created.

ops$tkyte%ORA11GR2> select * from user_jobs;

       JOB LOG_USER                       PRIV_USER
---------- ------------------------------ ------------------------------
SCHEMA_USER                    LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE
------------------------------ --------- -------- --------- -------- ---------
NEXT_SEC TOTAL_TIME B
-------- ---------- -
INTERVAL
-------------------------------------------------------------------------------
  FAILURES WHAT
---------- ------------------------------
NLS_ENV
-------------------------------------------------------------------------------
MISC_ENV                                                           INSTANCE
---------------------------------------------------------------- ----------
       264 OPS$TKYTE                      OPS$TKYTE
OPS$TKYTE                                                            13-OCT-12
09:58:15          0 N
sysdate+1
           dbms_refresh.refresh('"OPS$TKY
           TE"."MV"');
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURREN
CY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_L
ANGUAGE='AMERICAN' NLS_SORT='BINARY'
0102000200000000                                                          0





I'd stick with dbms_job personally.

Job Schedule in 11g

Prasanth, January 31, 2013 - 1:06 pm UTC

Hi Tom,
We recently migrated from 9ir2 to 11g (11.2.0.3.0). For doing a data aggregation/calculation and then loading data to a table we were using a job scheduler and it is asynchronously processed in a loop. In 9i the process was running correctly and all the data was getting processed. But in 11g looks the job queue is not waking up and is not repeating the execution.

Please find the code snippet used.

LOOP
SELECT count(1) INTO num_w FROM AGG_JOBS WHERE status = 'W';

IF num_w < p_nJobs THEN
begin
SELECT first_rowid, last_rowid
INTO first_id, last_id
FROM AGG_JOBS
WHERE status = 'N'
AND ROWNUM=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
GOTO outside;
END;
dbms_job.submit( job, 'PROD_AGG.PRODWORKER(JOB);',sysdate+1/24/60/60*2,null);

UPDATE AGG_JOBS
SET id = job, status = 'W'
WHERE first_rowid = first_id AND last_rowid = last_id;
COMMIT;

END IF;

dbms_lock.sleep(5);
<<outside>>

SELECT count(1) INTO num_w FROM AGG_JOBS WHERE status = 'W';

SELECT count(1) INTO num_c FROM AGG_JOBS WHERE status = 'C';

SELECT count(1) INTO num_n FROM AGG_JOBS WHERE status = 'N';

SELECT count(1) INTO num_e FROM AGG_JOBS WHERE status = 'E';

--Exit when the total records to process - total processed records - error in processed records = 0
EXIT WHEN num_all - num_c - num_e = 0;

The value of the job_queue_processes is 5. Does there any implicit value for job_queue_interval in 11g?

Please guide me on the issue.

Thank you.
Tom Kyte
January 31, 2013 - 2:55 pm UTC


please use the code button to surround code in code tags so the formatting doesn't get lost.


If you are saying that job queue processes is set to more than 0 and you see jobs in the dba_jobs - but those jobs are not running - please utilize support. They should be running.

is that what you are saying?

addition to qustion

pratik, August 18, 2014 - 10:08 am UTC

hi tom,
What if i need To schedule a job which will run in weekdays between 3pm to 5pm , in a interval of 15min.

oracle Scheduler

Nandhakumar, March 10, 2020 - 3:42 am UTC

Is it possible to postpond the scheduler after 2 hours if scheduler fails.
whenever it fails needs to postpond.

is it possible??
Connor McDonald
March 10, 2020 - 2:31 pm UTC

I'm assuming you mean postpone a single job?

You can catch errors in your job, and then use the DBMS_SCHEDULER api to change the next run date to sysdate+2/24 (ie, 2 hours from now)

Different Job Schedual

abc, November 04, 2021 - 10:17 am UTC

Hi. I have a query about job schedualing problem.
My query is to run job just 25 days in a month (not in first 5 days of month). In other Words I want to start month from 5th of that month.
Connor McDonald
November 09, 2021 - 4:02 am UTC

You could use a scheduler interval for this

FREQ=MONTHLY;BYMONTHDAY=5,6,7,....31

Ad-hoc scheduler job

A reader, November 15, 2021 - 7:41 am UTC


Hello,

Thank you for this treasure trove of all things Oracle :)
I consult askTom more than Oracle docs :P

I need your advise/help with a requirement that I have. We will be creating a scheduler job which will not have a pre-defined repeat_interval.

So, as you can guess, it will be run ad-hoc which will be decided each time the job executes. Min 5 mins to a max of 24 hrs.

I tested with a job without a "repeat_interval" and setting the "start_date" to (say) 5 mins after the 1st run. But the job runs during submit time and not as intended.

I know, an option is to drop it and create it based in the start_date but is there an easier way to accomplish the same?

V: 11.2.0.4.0 (Exadata)
Thanks in advance.
Cheers!

Chris Saxon
November 15, 2021 - 4:13 pm UTC

What exactly are you doing?

You can call dbms_scheduler.run_job to run it ad-hoc whenever you want

Tuan Phung, December 01, 2021 - 5:26 pm UTC

Hi Tom,
Can I schedule a job that I can't predict how long it will take? For example, we have procedure that will grab all new messages in a server, process and write them into our server's tables. Is it ok if we just schedule the job to run in every 3 minutes since we don't know how many messages that the job will get? I wonder if the volume is too high so the job couldn't complete within 3 minutes, will it miss its schedule then stop forever?

Scheduler job next run

A reader, January 14, 2022 - 7:24 am UTC

Hi,

DB version is 11.2.04 (will be upgrading to 19c soon). 

Requirement: Based on a procedure we need to perform some action in the DB. The procedure will be called by a scheduler job and will then set the next run date of the same job.

Problem: The procedure works as expected and is executed when the scheduler job runs as scheduled.
But, the problem we are facing is when scheduling the job for the next run. Now, the next run is not defined and is calculated when the job runs. As of now, the job does not have an end date or repeat interval and auto drop is FALSE.
So we set the next execution date using "start_date" but (as expected) while the job is running the procedure cannot enable the same job.
So, the job just sits there waiting in SUCCEEDED state.

Question: Is there a way where we can provide the repeat_interval as a date? Any other suggestion/workaround is welcome.

Thank you!

Connor McDonald
January 18, 2022 - 3:26 am UTC

As long as it has a repeat interval, then the job will persist. You can then alter the next start date from within the job itself, eg

SQL> create table t ( d date );

Table created.

SQL> create or replace
  2  procedure my_proc is
  3  begin
  4    insert into t values (sysdate); commit;
  5    dbms_session.sleep(30);
  6
  7    dbms_scheduler.set_attribute('DEMO','start_date',sysdate+60/86400);
  8  end;
  9  /

Procedure created.

SQL>
SQL> begin
  2      dbms_scheduler.create_job (
  3         job_name           =>  'DEMO',
  4         job_type           =>  'PLSQL_BLOCK',
  5         job_action         =>  'my_proc;',
  6         start_date         =>  sysdate,
  7         repeat_interval    =>  'FREQ=YEARLY',
  8         enabled            =>  true,
  9         comments           =>  'Run once');
 10  end;
 11  /

PL/SQL procedure successfully completed.


So my job will run for 30 seconds, and then tell itself to start again in 1 min time. I wait for a few mins and check T

SQL> select * from t;

no rows selected

SQL>
SQL> /

D
-------------------
18/01/2022 11:22:16

SQL> /

D
-------------------
18/01/2022 11:22:16
18/01/2022 11:23:46


That is one neat trick

A reader, January 18, 2022 - 7:13 am UTC


Thank you Connor. That is indeed a neat trick to schedule the job.

I had also tested on another approach where I was calculating the FREQ=DAILY and then provide the relevant values for BYHOUR, MINUTE and SECOND.

That too seems to be getting the job done.

Thanks for your advise and help as always :)

Cheers!

Connor McDonald
January 19, 2022 - 12:52 am UTC

glad we could help

JOB submitted using DBMS_JOB in Oracle 19c fails to run

Sam, April 06, 2022 - 2:15 am UTC

Hi

I have migrated an oracle database from 12.2 to 19.3 successfully.

The oracle application daily/weekly jobs that existed in old database and created using "DBMS_JOB" are running fine.

However, I found a problem in running of jobs submitted using DBMS_JOB in PL/SQL procedures..

When job runs an entry is created in DBA_SCHEULER_JOB but not in USER_JOBS though.

However, when it runs it fails in Oracle 19c. The reason of error is "no data found" due to job number not found.

Basically, I have PACKAGE.PROC1(test_no) that uses a test number and processes data for that test and submits an email job to send the user an email message.
The JOB ID is stored in the EMAIL_LOG table.

code...
dbms_job.submit(l_job,'MSG_EMAIL.BACKGROUND_SEND_CUST_EMAIL(JOB);');
code..


When this job runs it query the EMAIL_LOG table by JOB_ID and it is failing at that line. It seems the scheduler is not storing the job_id with the call so this statement in the code fails.

SELECT * INTO l_rec FROM email_log WHERE job_id = p_job;

How would you fix this using DBMS_JOB in 19c or convert the same line to DBMS_SCHEDULER using a JOB_ID identifier?

Thanks,
Connor McDonald
April 08, 2022 - 2:27 am UTC

Check this post

https://connor-mcdonald.com/2020/09/03/dbms_job-and-19c-code-changes-needed/

But note the last part - we fixed the issue in a later update. It would be best to move to that RU

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library