Skip to Main Content
  • Questions
  • DBMS_SCHEDULER: Emailing after inserting into database tables

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ruxandra.

Asked: October 12, 2004 - 3:10 pm UTC

Last updated: October 13, 2018 - 4:16 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Hello Tom,

You provided an example for "Emailing after inserting into database tables.";

I’m working with 10g. 10g documentation recommends using DBMS_SCHEDULER.

The code you provided using DBMS_JOB is listed below. Would be possible to provide an example that shoes how to replace this code using DBMS_SCHEDULER?


You would use UTL_SMTP in order to send an mail (easy -- search this site for
utl_smpt).

I would recommend writing a stored procedure that takes a NUMBER as input. It
would use this NUMBER to query a record that contains the necessary inputs to
generate and send the email. So, you would create a table:

create table email_parameters
( id number primary key,
<any data you needed to send email here>
)
/

And then put a trigger on the orders table:

create trigger send_email
after insert on orders for each row
declare
l_job number;
begin
dbms_job.submit( l_job, 'SEND_EMAIL( JOB );' );
insert into email_parameters values ( l_job, :new.whatever-you-need );
end;
/


and then a procedure send_email( p_job in number )
is
l_rec email_parameters%rowtype;
begin
select * into l_rec from email_parameters where id = p_job;

... format an send email using utl_smtp;

delete from email_parameters where id = p_job;
end;
/



That'll make it so that your email gets sent right after you commit (it is
transactional -- if you rollback the insert into orders -- no email gets sent,
if you commit, email gets sent)


remember to set job_queue_processes and job_queue_interval on the server!


and Tom said...

for a one off job like this -- no schedule, i'd stick with (well, i have stuck with) dbms_job.

For this purpose, dbms_job is just "too easy".


</code> https://asktom.oracle.com/Misc/oramag/on-numbers-and-analytics.html <code>
details the new scheduler, but it is a bit "overkill" perhaps for a one time job.

(job_queue_interval is not needed/used anymore)

Rating

  (56 ratings)

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

Comments

10g has UTL_MAIL...

Adrian, October 13, 2004 - 3:59 am UTC

If you are using 10g, then perhaps you'd like to take a look at the new UTL_MAIL package, which is much simpler to use than UTL_SMTP ( apparently - I've not tried it yet ! ).

</code> http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/u_mail.htm#ARPLS384 <code>

Regards
Adrian

dbms_scheduler.auto_purge

A reader, December 21, 2004 - 6:45 am UTC

Hi

When Oracle 10g is installed by default there are two scheduled jobs, PURGE_LOG_PROG and GATHER_STATS_PROG.

I know what GATHER_STATS_PROG does but not PURGE_LOG_PROG, by looking the program definition it calls dbms_scheduler.auto_purge which purge logs, what logs is it referring to?!

There is nothing about this in the docs!

thx

Tom Kyte
December 21, 2004 - 9:09 am UTC

select text from all_source where name = 'DBMS_SCHEDULER'
and type = 'PACKAGE'
order by line
/

....
-- The following procedure purges from the logs based on class and global
-- log_history
PROCEDURE auto_purge;
........


it automates the purging of the dbms_scheduler logs -- so they don't grow forever.


DBMS_SCHEDULER

Satheesh Babu.S, June 14, 2005 - 2:18 am UTC

Tom, We have requirement. We are scheduling some jobs. But we wanted to schedule it based on cpu usage. If the cpu usage is more than some percentage we wanted to pause the job and restart when the cpu usage reduces. Is there a way to do that using dbms_scheduler.

Thanks and Regards,
Satheesh Babu.S


Tom Kyte
June 14, 2005 - 9:49 am UTC

no, the closest would be the resource manager - as the cpu gets near 100% utilization, the resource manager can be used to control who gets how much of what resource.

see the admin guide.

job_queue_interval in 10g

Ravindran B, July 01, 2005 - 11:02 am UTC

Hi Tom,

We are using an application which uses job_queue interval extensively to fire jobs immediately. (we set this to 1). In 9i, we use the hidden parameter to set this value.

Now, we are in a great demand to migrate our DB to 10g. We learn that this parameter is made obsolete in 10g. Is there any way to still use this parameter (of course with value =1) or what is the alternative approach?

Tom Kyte
July 01, 2005 - 12:04 pm UTC

it was made obsolete in 9i (that was the _)

you shouldn't be mucking with it. no. it never made sure jobs were fired immediately anyway -- if you need jobs fired "immediately", you should have been using AQ (queues) and your own dequeue processes doing the work.

JOB_QUEUE_INTERVAL vs DBMS_SCHEDULER

Ravindran B, July 25, 2005 - 11:45 am UTC

Tom,

You advised earlier that we have to use AQ to replace JOB_QUEUE_INTERVAL and
dbms_job in 10g.

I recently read something on DBMS_SCHEDULER which can replace the dbms_job in
10g. Is it true that DBMS_SCHEDULER can run the job immediately as it is
scheduled? (i.e) Achieve the dbms_job.submit and _JOB_QUEUE_INTERVAL=1 of 9i?



Tom Kyte
July 25, 2005 - 1:19 pm UTC

look in the other place you put this same question.

(and I never said that)

A reader, August 30, 2005 - 4:52 pm UTC


dbms_scheduler output ?

Stefan, October 05, 2005 - 6:35 am UTC

Hi Tom,

is there no way to capture output of an executable run through dbms_scheduler ?

i.e. i want to run /bin/ls and see the output of that command "somewhere" :)

what i found out so far is that dbms_scheduler can return error messages from failed commands, so it technically should be able to return stdout as well... i just can't seem to find anything in the docs and on the net about it.

maybe you know some workaround ? (other then the jsp you already have here on asktom, to run os commands - i'd rather not be using that if it's possible with built-in packages, and i've been having odd problems with it; one program would run fine, and others would not, using the same code to execute them).





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

redirect and utl_file/bfile the redirected output.

Stefan, October 05, 2005 - 7:56 am UTC

hey,

i figured you'd say that :)

though i was hoping there was a more elegant / secure solution...

anyway, i heard you'll be speaking in switzerland in december ? :)

regards,

Stefan

Tom Kyte
October 05, 2005 - 10:57 am UTC

Yes, I will be. december 7-9 I believe it is.

Will dbms_job be deprecated???

marc, February 23, 2006 - 5:43 pm UTC

In the begging of this question your wrote "for a one off job like this -- no schedule, i'd stick with (well, I have stuck
with) dbms_job." Is this your opinion using 10gR2. I have a need for a job to be created, run and dropped(spawning a process). I would like to use dbms_job for its simplicity in this need, but I was reading "Expert Oracle PLSQL" and the book said dbms job will be deprecated and we should use DBMS_SCHEDULER. Is this true?

Tom Kyte
February 23, 2006 - 8:15 pm UTC

I am myself still using dbms_job. I like it, it is simple. The deprecation notice hasn't been written yet. (I have a grip about the scheduler package that needs to get fixed before I like it. It should not commit but it does)

Error while running the job

Vikas Khana, March 21, 2006 - 10:37 am UTC

Hi John,

I am trying to call a .sh script from the dbms_scheduler.run_job and Oracle is throwing the exception:

begin
dbms_scheduler.create_job
(
job_name => 'ADDM_REPORT',
job_type => 'EXECUTABLE',
job_action => '/opt/app/oracle/scripts/addm.ksh',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=60',
enabled => true,
comments => 'Generating ADDM report on an hourly basis'
);
end;
/

sys@ADAMDW>exec dbms_scheduler.run_job('ADDM_REPORT');
BEGIN dbms_scheduler.run_job('ADDM_REPORT'); END;

*
ERROR at line 1:
ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied
ORA-06512: at "SYS.DBMS_ISCHED", line 148
ORA-06512: at "SYS.DBMS_SCHEDULER", line 374
ORA-06512: at line 1

Can you please let us know, what is wrong and how we can get away with this error.

I thought it could be privilege problem, but SYS account also faced the same problem!

Thanks,


Tom Kyte
March 22, 2006 - 2:27 pm UTC

did you grant yourself the relevant permissions?

</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#sthref6657 <code>

you need create external job

dbms_scheduler commits?

glenm, October 24, 2006 - 5:55 pm UTC

I've seen your comment above about the gripe you have with dbms_scheduler performing a commit when it should not.

I can't find any documentation which points this out. I was relying on transactional control with job submission and it appears I've been blown away by this (and I've blamed the client programmers for not rolling back after a tablespace fillup exception in DEV).

Can you please point me to doco, tell me which actual procedure(s) in dbms_scheduler performs a commit, tell me if it is planned to be fixed, and/or any workaround?

Any help would be greatly appreciated - the fact that your message above is the only place I can find it mentioned already has saved me a heap of work. I'll now attempt to prove the problem myself and watch each call carefully for the commit.

Thanks

Tom Kyte
October 25, 2006 - 9:32 am UTC

looks like they did not note that. Please utilize support for this one. Needs to be "fixed" (the documentation).

even dbms_job does an implicit COMMIT after running a job

Pasko, October 25, 2006 - 1:40 pm UTC

Hi Tom,

we have already discussed this, that even the good old dbms_job performs an implicit commit after running a Job.

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


Tom Kyte
October 25, 2006 - 4:07 pm UTC

umm, dbms_job does not commit.

A job when run will have it's transaction ended. That is expoected, normal, correct.

But when you CALL dbms_job - to submit a job - dbms_job.submit DOES NOT COMMIT.

So, no, dbms_job does not commit.

Commit in DBMS_JOB

glenm, October 25, 2006 - 6:44 pm UTC

Thanks Tom,

I will raise the documentation bug with support.

I was really hoping you would tell me that this huge oversight and limitation in the dbms_scheduler package (which according to pl/sql packages and types reference - "The DBMS_JOB package has been superseded by the DBMS_SCHEDULER package"), would be fixed in the next release (eg. 10.3).

Thinking through what I do with this package, I can't believe how limiting it is to have transaction control taken away from the client by this new package.

I have actually considered the horrible thought of submitting a dbms_job job which would then submit a dbms_scheduler job, to at least give me back transactional control.

For info, I did some brief testing and even a submit of a job with enabled=>false, still performs a commit.

Tom Kyte
October 25, 2006 - 9:45 pm UTC

I agree, I don't think it should commit either - but that will be an "enhancement" at this point

Timezone

HItesh Bajaj, November 02, 2006 - 6:35 am UTC

Hi Tom,

We have a job scheduled to run at 2:00 AM PST, and needs to repeat on a daily basis. Since it involves Timezone I persisted with dbms_scheduler rather than dbms_job

BEGIN
sys.dbms_scheduler.disable( '"OLTP"."RI_CHECKING"' );
sys.dbms_scheduler.set_attribute( name => '"OLTP"."RI_CHECKING"', attribute => 'repeat_interval', value => 'new_time(sysdate+1,''GMT'',''PST'')');
sys.dbms_scheduler.set_attribute( name => '"OLTP"."RI_CHECKING"', attribute => 'start_date', value => to_timestamp_tz('2006-11-02 02:00:00 PST', 'YYYY-MM-DD HH24:MI:SS TZR'));
sys.dbms_scheduler.enable('"OLTP"."RI_CHECKING"' );
END;

The database is in UTC, so I have used the function new_time() to convert a date from GMT to PST before execution so that when time zone changes back to PDT the job should execute 1 hr earlier based on the machine time change.

Looking at the dbms_scheduler_jobs

Job_Name : RI_CHECKING
Start_Date : 02-NOV-06 02.00.00.000000 AM PST
Repeat_Interval : new_time(sysdate+1,'GMT','PST')
Last Start Date : 02-NOV-2006 02.00.00.092652
Next Run Date : 03-NOV-06 02.00.00.000000 AM +00:00

Will this solve the purpose. Please do let us know your suggestions.

Thanks

TimeZone Changes

Hitesh Bajaj, November 02, 2006 - 8:44 pm UTC

Hi Tom,

Can you please respond to the question posed.

Thanks


Tom Kyte
November 02, 2006 - 9:15 pm UTC

well, if you always use pst, you'll get pst always.

is that what you want.

TimeZone Changes

Hitesh, November 03, 2006 - 5:42 am UTC

Hi Tom,

Just to be more explicit, I need to run a batch job which is this PL/SQL procedure wrt. TZ when it is PDT or PST.

This job was scheduled to run at 2:00 AM PDT everyday, but due to shiftover from PDT to PST it continued to run at the same time, because the database is in UTC.

If the job needs to run 2:00 AM PDT then it should run at 3:00 AM PST, delayed by a gap of 1 hr.

During April, when the TZ will again shift back to PDT it should run at 2:00 AM PDT as against 3:00 AM PST.

This is what is required, when submitting a job via database job. The cron entries are specific to the machine time and automatically runs 1 hr behind, when compared to IST.

Just looking how can we achieve this via database job.What should be the repeat interval so that it can cater to all timezones.

Thanks

Tom Kyte
November 03, 2006 - 10:40 am UTC

sorry, but you are talking in circles sort of.

do you just want it to run at 2am PACIFIC TIME (doing the daylight savings when appropriate).

doubt you want to use new_time, more likely, you want to convert the database time to "pacific time" (no daylight or standard specified)

TimeZones

Hitesh Bajaj, November 04, 2006 - 5:37 am UTC

Hi Tom,

We have these 2 jobs being executed on the database (dbtimezone being UTC) at folling times:

TimeZone being PDT:

1.Job A being executed at 2:00 AM PDT which used to be 2:30 PM IST.
2.Job B being executed at 3:00 AM PDT which used to be 3:30 PM IST.

This used to happen till change when the TZ has been shifted to PST from PDT.Now since clocks have moved ahead by an hr, we also want these jobs should move ahead wrt. IST times ie.

1.Job A should get executed at 2:00 AM PST which should be 3:30 PM IST.
2.Job B should get executed at 3:00 AM PST which should be 4:30 PM IST.

I hope this makes the situation clear, if possible can you help us out.

Thanks

Tom Kyte
November 04, 2006 - 12:26 pm UTC

if you just use pacific.....

if you are going to use a specific timezone like pdt pst - you are on your own, you adjust.

DBMS_JOB vs DBMS_SCHEDULER

Guest, November 14, 2006 - 2:49 pm UTC

Hi Tom,

When i created a job using dbms_job in my schema i am able to create it but when i create using dbms_scheduler, it gives me an error "insufficient privilege".

I have not given the "create job" privilege, then how it works with DBMS_JOB?



Tom Kyte
November 15, 2006 - 6:46 am UTC

dbms_job does not create "scheduler jobs", dbms_job is different, dbms_job is not dbms_scheduler



Scheduling Question

A reader, January 24, 2007 - 10:09 am UTC

Hi Tom,

I love your site and it has been so much help in the past. I have searched around for information pertinent to this, but did not find anything specific to my need so I am hoping that you can help me. Here is my situation:

I have 3 jobs that run using dbms_scheduler.

1) The first runs a procedure every 10 minutes.
2) The second runs a procedure once a day.
3) The third dequeues a message from a queue and runs a package that can take anywhere from 1 hour to 8 hours to complete.

Currently, my job_queue_processes is set to 1. But if item 3 is running nothing else runs. What I want to have happen is this. I want to have items 1 and 2 run according to their schedules, but I do not want item 3 to dequeue a second message until the job for the first message is complete, and if I increase my job_queue_processes to something other than 1 then that could happen. Can you please tell me how to accomplish this? Thanks in advance.

mismatch between last_start_date and next_run_date

deba, May 14, 2007 - 11:15 am UTC

Hi Tom,

The following is my prcedure:
create or replace procedure das_lock as
begin
dbms_lock.sleep(120);
end;
/

Now I have created the following job ( keeping schedule inline to use pls/sql expression in repeat_interval ):
begin
dbms_scheduler.create_job
(job_name => 'stowner.deba_job1',
job_type => 'STORED_PROCEDURE',
job_action=> 'das_lock',
start_date=> sysdate,
repeat_interval=> 'sysdate+ 1*(1/(24*60))',
enabled=>true,
auto_drop=>false,
comments=>'deba');
end;
/

Now what I can see that last_start_date is always greater than next_run_date and this continues. But it should not be the case. In 9i, next_run_date was always correct. Please give your valuable input. Is it correct behaviour ?

See the output of the sql below:
select job_name,state,start_date,last_start_date,next_run_date,run_count from DBA_SCHEDULER_JOBS;

job_name state start_date last_start_date next_run_date run_count
DEBA_JOB1 RUNNING 14-MAY-07 03.56.46.000000000 PM GMT+05:30 14-MAY-07 04.02.47.230460000 PM GMT+00:00 14-MAY-07 04.01.47.000000000 PM GMT+00:00 3


Thanks
Deba
Tom Kyte
May 14, 2007 - 4:54 pm UTC

In 9i, dbms_scheduler did not exist, so - I'm confused as to how it could have always been correct?

mismatch between last_start_date and next_run_date

Deba, May 15, 2007 - 12:30 am UTC

Hi Tom,

Yes you are right. But what I tried to say that in 9i, in case of Oracle Background job ( dbms_job ), next_date of dba_jobs was always correct , not like this mismatch even if job takes more time than what is aussumed through interval . So in 10g , this is correct behaviour ?

Deba
Tom Kyte
May 15, 2007 - 3:58 pm UTC

if you compare apples to apples - dbms_job to dbms_job, you should not see any differences.

if you compare apples to flying toaster ovens - dbms_job to dbms_scheduler, I would not expect to see the same thing.

Now, I cannot really read your output above (use the CODE button for a courier font), but it would seem the job is currently RUNNING, so anything about future runs could certainly be expected to be in flux.

mismatch between last_start_date and next_run_date

A reader, May 15, 2007 - 2:26 pm UTC

I belive the next_run_date is not updated until the job finishes.

Best possible way to schedule jobs

Maverick439, June 14, 2007 - 2:32 pm UTC

I have a package with 10-12 stored procedures and I want to schedule them as jobs. They are not dependent on each other and can be scheduled in any order. My question is Shall I just use dbms_scheduler.create_job() for each job and be done with it or is there a better way of handling things like these?

Also, I have read your comment above:

---------QUOTE---------------------------------
Followup   February 23, 2006 - 8pm US/Eastern:

I am myself still using dbms_job.  I like it, it is simple.  The deprecation notice hasn't been 
written yet.  (I have a grip about the scheduler package that needs to get fixed before I like it.  
It should not commit but it does) 

---------END QUOTE---------------------------------

So, is there still a problem with scheduler?

BTW, I'm using Oracle 10g rel 2. Any help is greatly appreciated.


Tom Kyte
June 14, 2007 - 4:10 pm UTC

it commits. I do not like that.

if the fact that it commits fits in with your transaction logic, feel free to use it - the interval scheduling is more sophisticated than dbms_job.

Best possible way to schedule jobs

Maverick439, June 14, 2007 - 5:05 pm UTC

Thanks for responding Tom,
But I am also interested in other part [main part] of my question :-)
What's the best possible way to do that?


Thanks,
Tom Kyte
June 14, 2007 - 8:30 pm UTC

they are all equally "best". pick one.

DBMS_JOB in 11g...

Chris Poole, June 20, 2007 - 9:15 pm UTC

Hi Tom,

I completely agree with your concern about dbms_scheduler, the fact that it commits. I use your dbms_job 'trick' to make non transactional things like email transactional all the time.

So what do you make of Metalink Note 370648.1? It's actually a note on a bug to do with v$sql but contains this worrying comment:

"...dbms_job problem is not to be solved in 10g and from 11g version on, dbms_job will be just a wrapper for dbms_scheduler."

This implies that the current dbms_job functionality will be removed and dbms_job is just a wrapper package to dbms_scheduler, in which case we are stuck with the commit and these solutions will break.

?

Chris

http://www.chrispoole.co.uk




Tom Kyte
June 21, 2007 - 10:24 am UTC

we'll have to see when it becomes available.

Thanks for the question regarding "DBMS_SCHEDULER

Murali, July 20, 2007 - 11:11 am UTC

Tom,

We are using oracle 10gr2, We have serice of 240 Data converion PACKGED procedures. we would like to make automation of Data loading using dbms_scheduler. Each procedure return "return code" i.e return parameter for sucess execution 0 and fail 64.
i need to EXECUTE ALL PROCDURE autoload i.e auto run if my previous proceudre return 0. i would like to know is any way we can capture return code in dbms-scheduler and run next job if previous job run sucess.

THANKS,
Tom Kyte
July 20, 2007 - 5:05 pm UTC

there is not such thing as a return code.

Your procedures might return an out parameter that is of type number that you have decided to place the values 0 and 64 into - subverting the entire process of exceptions and taking us back to the 1980's programing wise - but there isn't anything called a "return code" in PLSQL.


schedule yet another procedure that contains code like

begin
p1( ...., return_code );
if ( return_code = 64 ) then raise program_error; end;
p2( ..., return_code );
if ( return_code ......


murali, July 21, 2007 - 3:45 am UTC

its correct ,but i have some of paralleljobs need to execute and schedule using dbms_schedular.

1) proce_1( retun code)-->> level-1
if proce_1 return code is 0 i need to auto execute
proce_2 and Proce_3 together i.e parallel -- Level-2
if proce 2 and proce_3 sucess i.e both retun 0. then need to auto execute
proce 4 proce 5-- Level 3

if proce 4 run sucess then need to auto execute proce-6 -- Level-4

I think if use normal plsql i can't exeucte parallel jobs together. pl/sql sequence jobs can no issue.

pls advise How to pass return value of my procedure to dbms_scheduler to auto execute next job

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

think about it, you'll have to have a control table of some sort that YOU process that YOU intrepret

there is no such thing as a return code. period, there is no way the scheduler could understand "your" return codes.


say you wanted to run p1, and then (p2,p3,p4) in parallel, and then p5 after p2,p3,p4 finish.

You would need a table for p2,p3,p4 to log into.

p1 would run and then schedule p2,p3,p4 to run.

p2,p3,p4 would each run, their last step would be to schedule p5 (or some thin p5 you create specially for the next use). p5 would run after the first completes, it would query the log table and discover that only one of p2,p3,p4 has executed so far. It exits.

another would finish and schedule this p5. same thing, p5 exits.

third time is a charm.


Or, you could use BPEL in the application server - not in the database - to control business process flow as well. But that would be an application server thing.

what about Scheduler Job Chains

Mwakuye, July 22, 2007 - 7:10 am UTC

Hi Tom,
i think in addition to what you have already suggested to Murali, another solution would be the use of Scheduler Job Chains , new in 10GR2.

I saw this feature being demonstrated with an Example here:
http://www.databasejournal.com/features/oracle/article.php/3593151

The separate procedures would the Chain-Steps and the return codes would be the Event-Conditions.

What do you think about this Solution

Regards



Tom Kyte
July 22, 2007 - 11:23 am UTC

the chains are serial progressions:

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/schedover.htm#sthref3496
...
Chains

A chain is a grouping of programs that are linked together for a single, combined objective. An example of a chain might be "run program A and then program B, but only run program C if programs A and B complete successfully, otherwise run program D." A Scheduler job can point to a chain instead of pointing to a single program object.
.......


they wants

run A and B concurrently, then C

Job_Action of failed scheduler jobs?

User, September 17, 2007 - 11:06 am UTC

Hi Tom,

Is there a way to find job_action (detail on what was executed) of failed scheduler jobs?

We're on 10g Rel 2.

There's a job_action in "dba_scheduler_jobs" but that information is not available in "dba_scheduler_job_run_details" or "dba_scheduler_job_log" for any completed or failed jobs. Where is job_action information stored after a job is executed?

Thanks.

Issue with commit of DBMS_SCHEDULER

Stewart Bryson, December 03, 2007 - 10:45 am UTC

Tom:

I was wondering if you had gotten any update on the COMMIT issue from the development team? Have they identified it as a bug or simply a feature? Also, were you able to verify whether 11g removed the DBMS_JOB functionality, making it essentially a wrapper for DBMS_SCHEDULER?

What would be the repercussions of submitting a DBMS_SCHEDULER job with an autonomous transaction? In my testing, it seems to work, but I could be missing bigger-picture issues.

Thanks as always.
Tom Kyte
December 03, 2007 - 11:43 am UTC

it is a feature of the scheduler, but in 11g, the infrastucture that runs the job will be the scheduler, but dbms_job fortunately is still transactional.

the autonomous transaction should not be used, it breaks the transaction! what happens now when you rollback? And what happens when your trigger fires more than once (your row triggers might be invoked twice - once rolled back and once again - but if you use an autonomous transaction.... you'll do the job twice)

This is 11g release 1:

ops$tkyte%ORA11GR1> create table t ( x int );

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA11GR1> variable n number
ops$tkyte%ORA11GR1> exec dbms_job.submit( :n, 'insert into t values ( 2 );' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select * from t;

         X
----------
         1

ops$tkyte%ORA11GR1> select job, what from user_jobs;

       JOB WHAT
---------- ------------------------------
        32 insert into t values ( 2 );

ops$tkyte%ORA11GR1> rollback;

Rollback complete.

ops$tkyte%ORA11GR1> select * from t;

no rows selected

ops$tkyte%ORA11GR1> select job, what from user_jobs;

no rows selected

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> insert into t values ( 1 );

1 row created.

that shows it is transactional, a rollback unqueues the job and rolls back the insert - dbms_job did not commit...

ops$tkyte%ORA11GR1> variable n number
ops$tkyte%ORA11GR1> exec dbms_job.submit( :n, 'insert into t values ( 2 );' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> commit;

Commit complete.

ops$tkyte%ORA11GR1> pause

ops$tkyte%ORA11GR1> select * from t;

         X
----------
         2
         1



and that just shows it "working"

Using DBMS_Scheduler to run multiple jobs

sajith, July 23, 2008 - 6:27 am UTC

Hi Tom,

Is it possible to schedule multiple jobs to run in a sequence. If so how could i achieve the same.

DBMS_SCHEDULER has to execute a PLSQL procedure first.
Then the scheduler should start off a shell script to do some calculations.
The Scheduler may be configured to run weekely or daily as per needs of the client.

Please give your valuable suggestions on how this can be achieved as most examples i see are individual schedules.

regards ..
Sajith

Atif, April 05, 2009 - 5:51 am UTC

Hi Tom, Is there any way of sending mail after RMAN backup is completed or failed through OEM 10gr2. I was able to this in 9i using OEM but 10g cant find it.
I already have configured and tested my mail in notification methods of OEM.I did a lot of googling and searched on metalink but couldnt find. Kindly suggest.



Conditional job submission using DBMS_SCHEDULER

Clark Pearson, May 28, 2009 - 6:36 am UTC

Hi Tom,

In your followup:
>> Followup July 21, 2007 - 9am US/Eastern
you commented on the fact pl/sql does not use return codes and the scheduler has no way of seeing RCs. Ok.

I have used the Scheduler to conditionally submit jobs based on another one completing:
event_condition => 'tab.user_data.object_name = ''JOB1'' AND '
                || 'tab.user_data.event_type = ''JOB_SUCCEEDED'''


Now, I wish to submit an EXECUTABLE job based on the result of a plsql function call: it is not a failure if the plsql function says "job not required at this time", however I can see no intrinsic way of preventing submission of the executable without forcing a plsql exception (so that no JOB_SUCCEEDED event is raised).

Exceptions say to me "This job went wrong, it needs fixing," and so I am loathe to raise an exception.

Q.
If I was to log a return code into a table, can I then query the table in the event_condition?

Various snippets seem to suggest a query can be used in the event_condition but I am struggling to find any examples. I have read DBMS_SCHEDULER documentation a dozen times!

Thanks for your time.
Clark.
Tom Kyte
May 28, 2009 - 8:02 am UTC

well, in general,


column = <expression>

is part of a where clause, expression could be a literal (as you have) or many other things - including a scalar subquery.


so, have you tried 'tab.user_data.xxxx = (select ....)'

dbms_scheduler and return codes.

Clark Pearson, June 19, 2009 - 9:06 am UTC

Not been able to focus on this, hence delay, but I can now confirm that you can include a query in the event condition. However, the way I've used it is not quite how Tom suggested, instead it's like this...

... AND 0 = (<query returning prev job RC>)

I found an explicit statement on the Oracle forums (can't find the thread again now so sorry, no link) that you could include a query... so there you go, you can.

Cheers,
Clark.

DBMS_SCHEDULER manual job submission

Clark Pearson, June 22, 2009 - 7:41 am UTC

Hi Tom,

Now I have another scheduler question. As previously, I have event-triggered jobs, but let's say JOB2 fails after JOB1 triggered it to run.

So, we fix the problem with JOB2 and want to run it again. If you try running it using ..RUN_JOB in the foreground, the Scheduler returns an error:

BEGIN DBMS_SCHEDULER.RUN_JOB('CLOINK2',TRUE); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 1


This happens in the foreground, ie. with the use_current_session => TRUE flag set. If you run it in a background session, no error is visible to the user, the job never runs, but I think you do see the ORA-00942 error in the Oracle error log somewhere (if I go ask a DBA to check for me).

The dummy job returning the above error is simply running a PLSQL_BLOCK of "NULL;", it is absolutely NOT the application trying to access a non-existent table. As far as I can tell, the Scheduler is failing internally, and I can only guess it is something to do with the event triggers not getting resolved properly.

Q.
Is there a way to manually run an event-triggered job?

There is a script below you can run to set up a dummy schedule with 2 jobs, the 2nd of which is triggered by the first. You need the SCHEDULER_ADMIN role. Set the repeat_interval on the first so that it runs in the next minute or two, then you can monitor USER_SCHEDULER_JOB_RUN_DETAILS, and see that the jobs do run successfully when allowed to proceed according to the schedule.

Now try running CLOINK2 manually, as above, and it will fail.

Thanks,
Clark.

BEGIN

DBMS_SCHEDULER.REMOVE_EVENT_QUEUE_SUBSCRIBER('CLOINK1');
DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER('CLOINK1');

DBMS_SCHEDULER.CREATE_JOB
(   job_name        => 'CLOINK1'
,   job_type        => 'PLSQL_BLOCK'
,   job_action      => 'NULL;'
,   enabled         => TRUE
,   repeat_interval => 'FREQ=DAILY;BYHOUR=12;BYMINUTE=7;'
);

DBMS_SCHEDULER.SET_ATTRIBUTE( 'CLOINK1',
    'raise_events', DBMS_SCHEDULER.JOB_SUCCEEDED
                  + DBMS_SCHEDULER.JOB_FAILED
);

DBMS_SCHEDULER.REMOVE_EVENT_QUEUE_SUBSCRIBER('CLOINK2');
DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER('CLOINK2');

DBMS_SCHEDULER.CREATE_JOB
(   job_name        => 'CLOINK2'
,   job_type        => 'PLSQL_BLOCK'
,   job_action      => 'NULL;'
,   enabled         => TRUE
,   event_condition => 'tab.user_data.object_name = ''CLOINK1'' AND '
                    || 'tab.user_data.event_type = ''JOB_SUCCEEDED'''
,   queue_spec      => 'SYS.SCHEDULER$_EVENT_QUEUE,CLOINK2'
);

DBMS_SCHEDULER.SET_ATTRIBUTE( 'CLOINK2',
    'raise_events', DBMS_SCHEDULER.JOB_SUCCEEDED
                  + DBMS_SCHEDULER.JOB_FAILED
);

END;
/

A reader, June 24, 2009 - 3:49 pm UTC

Lets say , I have a job ( dbms_scheduler) which has window of 2 hours.
The routine in the job takes over 2 hours . What will happen to the routine ?
Does Oracle stops the processing of the routine ?


Tom Kyte
June 26, 2009 - 10:29 am UTC

it depends


http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/scheduse.htm#sthref3643


A job that is running will not close when the window it is running in closes unless the attribute stop_on_window_close was set to TRUE when the job was created. However, the resources allocated to the job may change because the resource plan may change.

A reader, August 06, 2009 - 11:02 am UTC

I have created 256 jobs using dbms_scheduler to simulate DIY parallelism .

I have set the parameter ( job_queue_process ) to 8 in 7 nodes ( in RAC cluster ) .
Out of these 256 jobs , 69 were running concurrently .

Per my understanding , I should had only 56 jobs running concurrently .

Is my understanding correct ?

I am on 10.2.0.2 on Red Hat Linux
Tom Kyte
August 06, 2009 - 11:09 am UTC

if job queue processes was set universally in all instances to 8, you would have had 56 running.

therefore, I have to guess that 8 was not set universally - one of the instances at least had something else set.

query gv$parameter

A reader, August 06, 2009 - 11:24 am UTC

Tom ,

I have checked the gv$parameter before posting the question.
Thanks.. .

INST_ID NAME VALUE DISPLAY_VALUE ISDEFAULT

------- -------------------- ---------- --------------- ----------

7 job_queue_processes 8 8 FALSE

6 job_queue_processes 8 8 FALSE

5 job_queue_processes 8 8 FALSE

1 job_queue_processes 8 8 FALSE

2 job_queue_processes 8 8 FALSE

3 job_queue_processes 8 8 FALSE

4 job_queue_processes 8 8 FALSE


Tom Kyte
August 06, 2009 - 12:36 pm UTC

(I have no idea what people do or don't do - I just ask...)

it should not have happened then - is it repeatable? Are you sure that those values were in place when you started running? What did you do to measure the degree of concurrency?

A reader, August 06, 2009 - 9:17 pm UTC

I was looking at LAST_START_DATE , STATE , LAST_RUN_DURATION
in USER_SCHEDULER_JOBS. We are going to re run the job and post it if we are running into the same issue .

And will contact Oracle Support as well .

Thanks very much.
Tom Kyte
August 07, 2009 - 9:27 am UTC

what query did you use? It would be a complex one to ascertain that at an arbitrary point in time you had "X" things going... Maybe the query was misbehaving?

A reader, August 07, 2009 - 11:05 am UTC

Here the job name is OSDATAFIX001 ,.... OSDATAFIX256 .
I used this query .


SELECT job_name, last_start_date, last_run_duration, logging_level, state
FROM user_scheduler_jobs
WHERE job_name LIKE 'OSDATAFIX%'
ORDER BY job_name ASC



Tom Kyte
August 07, 2009 - 2:31 pm UTC

what query did you use to determine that 69 were running concurrently. that is a hard query to code - how did you ascertain that.

A reader, August 07, 2009 - 2:47 pm UTC

Based on the query output , I figured out the concurrent jobs were in the first half of the output.

Here is the partial list of output .
Removed the logging_level , state as it does not matter to the discussion.


OSDATAFIX_164 04-AUG-09 11.07.39.066271 PM -05:00 +000000000 01:08:25.644802
OSDATAFIX_246 04-AUG-09 11.07.39.648647 PM -05:00 +000000000 01:09:04.317551
OSDATAFIX_253 04-AUG-09 11.07.39.732743 PM -05:00 +000000000 01:08:37.654804
OSDATAFIX_239 04-AUG-09 11.07.40.315762 PM -05:00 +000000000 01:08:33.406073
OSDATAFIX_232 04-AUG-09 11.07.40.422131 PM -05:00 +000000000 01:05:11.971979
OSDATAFIX_225 04-AUG-09 11.07.40.501981 PM -05:00 +000000000 01:05:44.543824
OSDATAFIX_218 04-AUG-09 11.07.40.583434 PM -05:00 +000000000 01:08:38.610856
OSDATAFIX_204 04-AUG-09 11.07.41.169078 PM -05:00 +000000000 01:08:19.900675
OSDATAFIX_211 04-AUG-09 11.07.41.252234 PM -05:00 +000000000 01:08:56.292354
OSDATAFIX_243 04-AUG-09 11.07.41.402847 PM -05:00 +000000000 01:09:06.562692
OSDATAFIX_236 04-AUG-09 11.07.41.456508 PM -05:00 +000000000 01:09:34.028706
OSDATAFIX_229 04-AUG-09 11.07.41.499029 PM -05:00 +000000000 01:09:32.095757
OSDATAFIX_014 04-AUG-09 11.07.41.553430 PM -05:00 +000000000 01:09:58.287639
OSDATAFIX_250 04-AUG-09 11.07.41.586384 PM -05:00 +000000000 01:17:06.758170
OSDATAFIX_197 04-AUG-09 11.07.41.839438 PM -05:00 +000000000 01:09:59.724428
OSDATAFIX_234 04-AUG-09 11.07.42.022774 PM -05:00 +000000000 01:09:14.079796
OSDATAFIX_227 04-AUG-09 11.07.42.086905 PM -05:00 +000000000 01:10:13.240964
OSDATAFIX_213 04-AUG-09 11.07.42.130768 PM -05:00 +000000000 01:10:15.216603
OSDATAFIX_255 04-AUG-09 11.07.42.167964 PM -05:00 +000000000 01:10:21.857585
OSDATAFIX_220 04-AUG-09 11.07.42.220109 PM -05:00 +000000000 01:08:36.263358
OSDATAFIX_222 04-AUG-09 11.07.42.222233 PM -05:00 +000000000 01:10:47.247479
OSDATAFIX_248 04-AUG-09 11.07.42.252705 PM -05:00 +000000000 01:10:43.952779
OSDATAFIX_215 04-AUG-09 11.07.42.257750 PM -05:00 +000000000 01:09:26.235894
OSDATAFIX_241 04-AUG-09 11.07.42.306003 PM -05:00 +000000000 01:08:12.953474
OSDATAFIX_208 04-AUG-09 11.07.42.322609 PM -05:00 +000000000 01:07:47.449808
OSDATAFIX_201 04-AUG-09 11.07.42.435971 PM -05:00 +000000000 01:07:31.436413
OSDATAFIX_192 04-AUG-09 11.07.43.050217 PM -05:00 +000000000 01:08:16.740275
OSDATAFIX_206 04-AUG-09 11.07.43.062491 PM -05:00 +000000000 01:10:15.534315
OSDATAFIX_199 04-AUG-09 11.07.43.071210 PM -05:00 +000000000 01:12:15.651039
OSDATAFIX_214 04-AUG-09 11.07.43.233074 PM -05:00 +000000000 01:10:35.629404
OSDATAFIX_235 04-AUG-09 11.07.43.321118 PM -05:00 +000000000 01:10:38.275355
OSDATAFIX_256 04-AUG-09 11.07.43.375783 PM -05:00 +000000000 01:12:36.947204
OSDATAFIX_249 04-AUG-09 11.07.43.424468 PM -05:00 +000000000 01:09:27.167868
OSDATAFIX_228 04-AUG-09 11.07.43.494929 PM -05:00 +000000000 01:09:23.145571
OSDATAFIX_242 04-AUG-09 11.07.43.565208 PM -05:00 +000000000 01:09:07.733687
OSDATAFIX_221 04-AUG-09 11.07.43.611805 PM -05:00 +000000000 01:10:32.444201
OSDATAFIX_061 04-AUG-09 11.07.43.701933 PM -05:00 +000000000 01:09:43.426505
OSDATAFIX_237 04-AUG-09 11.07.43.837421 PM -05:00 +000000000 01:12:07.242445
OSDATAFIX_216 04-AUG-09 11.07.43.924004 PM -05:00 +000000000 01:11:02.363714
OSDATAFIX_251 04-AUG-09 11.07.44.000989 PM -05:00 +000000000 01:11:57.486791
OSDATAFIX_230 04-AUG-09 11.07.44.071316 PM -05:00 +000000000 01:12:43.504251
OSDATAFIX_244 04-AUG-09 11.07.44.144369 PM -05:00 +000000000 01:13:33.930940
OSDATAFIX_207 04-AUG-09 11.07.44.243690 PM -05:00 +000000000 01:10:10.503064
OSDATAFIX_193 04-AUG-09 11.07.44.249756 PM -05:00 +000000000 01:13:25.286277
OSDATAFIX_223 04-AUG-09 11.07.44.255879 PM -05:00 +000000000 01:12:41.290326
OSDATAFIX_200 04-AUG-09 11.07.44.306257 PM -05:00 +000000000 01:13:10.903767
OSDATAFIX_238 04-AUG-09 11.07.44.741206 PM -05:00 +000000000 01:11:15.348516
OSDATAFIX_231 04-AUG-09 11.07.44.826041 PM -05:00 +000000000 01:11:48.157056
OSDATAFIX_217 04-AUG-09 11.07.44.904654 PM -05:00 +000000000 01:13:01.728032
OSDATAFIX_110 04-AUG-09 11.07.44.985589 PM -05:00 +000000000 01:13:38.132027
OSDATAFIX_195 04-AUG-09 11.07.44.995440 PM -05:00 +000000000 01:29:01.244772
OSDATAFIX_209 04-AUG-09 11.07.44.995731 PM -05:00 +000000000 01:29:03.129929
OSDATAFIX_224 04-AUG-09 11.07.45.067278 PM -05:00 +000000000 01:30:13.770249
OSDATAFIX_202 04-AUG-09 11.07.45.081255 PM -05:00 +000000000 01:29:42.645344
OSDATAFIX_252 04-AUG-09 11.07.45.151019 PM -05:00 +000000000 01:29:15.748921
OSDATAFIX_186 04-AUG-09 11.07.45.211262 PM -05:00 +000000000 01:29:51.542863
OSDATAFIX_245 04-AUG-09 11.07.45.231331 PM -05:00 +000000000 01:28:43.418091
OSDATAFIX_247 04-AUG-09 11.07.45.293525 PM -05:00 +000000000 01:29:44.018749
OSDATAFIX_233 04-AUG-09 11.07.45.367634 PM -05:00 +000000000 01:31:05.945682
OSDATAFIX_254 04-AUG-09 11.07.45.454389 PM -05:00 +000000000 01:29:35.781887
OSDATAFIX_240 04-AUG-09 11.07.45.571823 PM -05:00 +000000000 01:16:58.963727
OSDATAFIX_196 04-AUG-09 11.07.45.976505 PM -05:00 +000000000 01:32:10.280317
OSDATAFIX_210 04-AUG-09 11.07.46.020652 PM -05:00 +000000000 01:31:22.065626
OSDATAFIX_203 04-AUG-09 11.07.46.076196 PM -05:00 +000000000 01:29:40.323564
OSDATAFIX_226 04-AUG-09 11.07.46.232716 PM -05:00 +000000000 01:32:57.104498
OSDATAFIX_219 04-AUG-09 11.07.46.304042 PM -05:00 +000000000 01:30:34.730553
OSDATAFIX_212 04-AUG-09 11.07.46.424406 PM -05:00 +000000000 01:31:16.207528
OSDATAFIX_198 04-AUG-09 11.07.46.466037 PM -05:00 +000000000 01:30:09.893346
OSDATAFIX_205 04-AUG-09 11.07.46.562457 PM -05:00 +000000000 01:31:53.849954


OSDATAFIX_194 05-AUG-09 12.22.44.892675 AM -05:00 +000000000 01:18:27.214132
OSDATAFIX_191 05-AUG-09 12.22.59.880754 AM -05:00 +000000000 01:23:33.248025
OSDATAFIX_190 05-AUG-09 12.23.13.829430 AM -05:00 +000000000 01:17:22.113361
OSDATAFIX_189 05-AUG-09 12.23.20.692269 AM -05:00 +000000000 01:16:49.596759
OSDATAFIX_188 05-AUG-09 12.23.26.899575 AM -05:00 +000000000 01:23:10.676828
OSDATAFIX_187 05-AUG-09 12.23.39.881750 AM -05:00 +000000000 01:16:52.157441
OSDATAFIX_185 05-AUG-09 12.23.39.883016 AM -05:00 +000000000 01:17:10.535695
OSDATAFIX_184 05-AUG-09 12.23.43.830457 AM -05:00 +000000000 01:16:50.301877
OSDATAFIX_183 05-AUG-09 12.23.54.894081 AM -05:00 +000000000 01:17:06.991330
OSDATAFIX_182 05-AUG-09 12.23.55.383892 AM -05:00 +000000000 01:18:17.122098
OSDATAFIX_181 05-AUG-09 12.23.57.830387 AM -05:00 +000000000 01:15:12.742728
OSDATAFIX_180 05-AUG-09 12.24.05.398640 AM -05:00 +000000000 01:23:44.534935
OSDATAFIX_179 05-AUG-09 12.24.07.333084 AM -05:00 +000000000 01:17:39.928641
OSDATAFIX_178 05-AUG-09 12.24.14.882315 AM -05:00 +000000000 01:16:58.647452
OSDATAFIX_177 05-AUG-09 12.24.19.956170 AM -05:00 +000000000 01:18:53.905055
OSDATAFIX_176 05-AUG-09 12.24.21.181531 AM -05:00 +000000000 01:18:38.330966
OSDATAFIX_175 05-AUG-09 12.24.24.677781 AM -05:00 +000000000 01:18:13.978537
OSDATAFIX_174 05-AUG-09 12.24.25.900905 AM -05:00 +000000000 01:15:52.037146
OSDATAFIX_173 05-AUG-09 12.24.32.841769 AM -05:00 +000000000 01:23:41.885774
OSDATAFIX_172 05-AUG-09 12.24.33.344157 AM -05:00 +000000000 01:17:30.853485
OSDATAFIX_171 05-AUG-09 12.24.36.402060 AM -05:00 +000000000 01:15:56.739098
OSDATAFIX_170 05-AUG-09 12.24.36.907189 AM -05:00 +000000000 01:17:19.182987
OSDATAFIX_169 05-AUG-09 12.24.39.732032 AM -05:00 +000000000 01:15:33.076707
OSDATAFIX_168 05-AUG-09 12.24.43.351991 AM -05:00 +000000000 01:19:27.346710
OSDATAFIX_167 05-AUG-09 12.24.44.685164 AM -05:00 +000000000 01:16:04.443839
OSDATAFIX_166 05-AUG-09 12.24.45.693405 AM -05:00 +000000000 01:23:54.448391
OSDATAFIX_163 05-AUG-09 12.24.52.358311 AM -05:00 +000000000 01:16:46.634885
OSDATAFIX_165 05-AUG-09 12.24.52.362885 AM -05:00 +000000000 01:16:48.927656
OSDATAFIX_162 05-AUG-09 12.24.55.197064 AM -05:00 +000000000 01:17:36.319580
OSDATAFIX_161 05-AUG-09 12.24.55.704339 AM -05:00 +000000000 01:16:49.795337
OSDATAFIX_160 05-AUG-09 12.24.56.202488 AM -05:00 +000000000 01:19:26.408654
OSDATAFIX_159 05-AUG-09 12.25.01.691444 AM -05:00 +000000000 01:14:55.569558
OSDATAFIX_158 05-AUG-09 12.25.04.679356 AM -05:00 +000000000 01:23:47.202376
OSDATAFIX_157 05-AUG-09 12.25.05.187964 AM -05:00 +000000000 01:18:36.161857
OSDATAFIX_156 05-AUG-09 12.25.15.701486 AM -05:00 +000000000 01:16:10.704605
OSDATAFIX_155 05-AUG-09 12.25.17.424637 AM -05:00 +000000000 01:18:08.581289
OSDATAFIX_154 05-AUG-09 12.25.17.930441 AM -05:00 +000000000 01:18:19.533338
OSDATAFIX_153 05-AUG-09 12.25.24.449458 AM -05:00 +000000000 01:19:11.911029
OSDATAFIX_152 05-AUG-09 12.25.25.179307 AM -05:00 +000000000 01:15:50.964818
OSDATAFIX_151 05-AUG-09 12.25.29.414620 AM -05:00 +000000000 01:22:44.809633
OSDATAFIX_149 05-AUG-09 12.25.37.000500 AM -05:00 +000000000 01:19:59.660358
OSDATAFIX_150 05-AUG-09 12.25.37.000530 AM -05:00 +000000000 01:16:49.071243
OSDATAFIX_148 05-AUG-09 12.25.43.503037 AM -05:00 +000000000 01:17:19.391370
OSDATAFIX_147 05-AUG-09 12.25.55.695219 AM -05:00 +000000000 01:17:25.647816
OSDATAFIX_146 05-AUG-09 12.25.58.995352 AM -05:00 +000000000 01:18:48.385456
OSDATAFIX_145 05-AUG-09 12.25.59.496706 AM -05:00 +000000000 01:16:05.500459
OSDATAFIX_144 05-AUG-09 12.26.10.717105 AM -05:00 +000000000 01:24:02.425921
OSDATAFIX_143 05-AUG-09 12.26.25.211209 AM -05:00 +000000000 01:17:26.305240
OSDATAFIX_142 05-AUG-09 12.26.27.443325 AM -05:00 +000000000 01:17:07.296478
OSDATAFIX_141 05-AUG-09 12.26.31.032367 AM -05:00 +000000000 01:17:12.707202
OSDATAFIX_140 05-AUG-09 12.26.35.213572 AM -05:00 +000000000 01:17:10.748333
OSDATAFIX_139 05-AUG-09 12.26.43.247080 AM -05:00 +000000000 01:17:48.906875
OSDATAFIX_138 05-AUG-09 12.26.44.527402 AM -05:00 +000000000 01:16:31.300708
OSDATAFIX_137 05-AUG-09 12.26.53.531055 AM -05:00 +000000000 01:22:58.706559
OSDATAFIX_136 05-AUG-09 12.27.05.716309 AM -05:00 +000000000 01:21:08.845549
OSDATAFIX_135 05-AUG-09 12.27.09.041103 AM -05:00 +000000000 01:15:30.973221
OSDATAFIX_134 05-AUG-09 12.27.09.041521 AM -05:00 +000000000 01:16:29.415384
OSDATAFIX_133 05-AUG-09 12.27.47.475267 AM -05:00 +000000000 01:18:47.841266
OSDATAFIX_132 05-AUG-09 12.28.53.490101 AM -05:00 +000000000 01:18:03.781921
OSDATAFIX_131 05-AUG-09 12.30.41.996873 AM -05:00 +000000000 01:15:39.836380
OSDATAFIX_130 05-AUG-09 12.30.42.489046 AM -05:00 +000000000 01:23:52.091330
OSDATAFIX_129 05-AUG-09 12.30.57.466194 AM -05:00 +000000000 01:17:42.469251
OSDATAFIX_128 05-AUG-09 12.31.22.475606 AM -05:00 +000000000 01:16:21.246025
OSDATAFIX_127 05-AUG-09 12.31.27.469132 AM -05:00 +000000000 01:16:39.787677
OSDATAFIX_126 05-AUG-09 12.31.27.973705 AM -05:00 +000000000 01:17:11.004343
OSDATAFIX_125 05-AUG-09 12.31.32.467466 AM -05:00 +000000000 01:17:48.155029
OSDATAFIX_123 05-AUG-09 12.31.37.462894 AM -05:00 +000000000 01:16:37.766651
OSDATAFIX_124 05-AUG-09 12.31.37.468744 AM -05:00 +000000000 01:23:37.001390
OSDATAFIX_122 05-AUG-09 12.31.47.471874 AM -05:00 +000000000 01:16:21.050808

Tom Kyte
August 07, 2009 - 3:39 pm UTC

duh, I knew this.

I was thinking dbms_job for some reason

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/schedadmin.htm#sthref3776

Scheduler Attribute max_job_slave_processes

The initialization parameter JOB_QUEUE_PROCESSES only applies to DBMS_JOB. When DBMS_SCHEDULER is used, the coordinator will automatically determine how many job slaves to start based on CPU load and the number of outstanding jobs. In special scenarios a dba can still limit the maximum number of slaves to be started by the coordinator by setting the MAX_JOB_SLAVE_PROCESSES with the DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE procedure.

A reader, August 08, 2009 - 9:30 pm UTC

Thanks a lot , Tom .


scheduler jobs not running after db restart

alan, December 15, 2009 - 11:20 am UTC

Hi tom,

I am on linux, Oracle Database 10g Release 10.2.0.1.0 - 64bit Production.

To my dismay this morning, all my scheduler jobs are not running for 1 week!

I check the USER_SCHEDULER_JOBS -- all are ENABLED
i check the USER_SCHEDULER_JOB_LOGS -- all succeeded..
but the last logged record date is of 4TH DEC!..

so i went googling around, checking if JOB_QUEUE_PROCESSES is needed but realise no matter i set value to a non-zero or zero value, the jobs are still not ran.

However, manually running the job works.
--------------------------------------------

o i went to trace the alert log.. on 4th DEC and i saw this ..


Fri Dec 4 01:06:33 2009
Shutting down instance: further logons disabled
Fri Dec 4 01:06:46 2009
Stopping background process CJQ0
Fri Dec 4 01:06:46 2009
Stopping background process MMNL
Fri Dec 4 01:06:47 2009
Stopping background process MMON
Fri Dec 4 01:06:55 2009
Shutting down instance (normal)
License high water mark = 122
Fri Dec 4 01:08:17 2009
Starting ORACLE instance (normal)
Fri Dec 4 01:09:21 2009
Starting ORACLE instance (normal)
Fri Dec 4 01:26:25 2009
MMNL absent for 1201 secs; Foregrounds taking over

--------------------------------------------------
i realise that after the database is restarted

1) the MMNL process did not get started.. - it say MMNL absent for 1201 secs; Foregrounds taking over
2) the MMON process did not get started..
3) the CJQ process did not get started...

q1) why doesnt the CJQ process get started ? is this the possible reason why the jobs aint running ?

q2)does MMNL and MMON process indirectly affects CJQ process ?

q3) How do i get them all started ?

Please advise
Regards,
Noob



Tom Kyte
December 15, 2009 - 1:25 pm UTC

cjq is the job queue processor - yes.

please utilize support for this, it should not be happening that way

dbms_scheduler implicit commit

Marat, January 30, 2010 - 9:09 pm UTC

Dear Tom,

could you suggest the best way to workaround an implicit commit of dbms_scheduler? Since dbms_job is superseded by dbms_scheduler - what is another way to have a job submitted inside a transaction?

Thanks!
Tom Kyte
February 01, 2010 - 10:17 am UTC

dbms_job is still there and working great, I'd use that.


I do use that.

AQ + dbms_scheduler

Marat Tolgambayev, February 01, 2010 - 7:29 pm UTC

Thanks Tom for your answer!

I've workarounded that using AQ. What do you think about my approach?
[transaction]->[put a message into a queue]
[listener]->[dequeue the message]->[create scheduler's job]


Thank you!
Tom Kyte
February 02, 2010 - 12:09 pm UTC

you could do that

but it would involve a lot more work, wouldn't it...

Scheduler via AQ

Marat Tolgambayev, February 02, 2010 - 4:28 pm UTC

Actually, not much work. It takes no more than 1 hour to organise a message type, a queue, and a dequeue procedure (which, in turn, creates a job). So, now I have a complete job functionality (with contemporary DBMS_SCHEDULER instead of DBMS_JOB), working inside a transaction (in fact, sending a queue message is inside a transaction).
Tom Kyte
February 03, 2010 - 9:30 am UTC

it takes one nanasecond or less to have the job queue.


and you have to maintain your code *forever*. Your one hour - just the tip of the iceberg. In three months - when you've moved on - someone will be maintaining your overly complicated (overly complicated for the problem at hand) queue - a queue which was simply not necessary and not really proper.


You do not have complete job functionality, you have a bunch of handwritten code that must be maintained over the long haul, which will break one day and have to be fixed, which must be monitored separate from everything else (not part of the normal infrastructure).

so dbms_job will stay ?

Sokrates, February 03, 2010 - 10:13 am UTC

it can't be declared as "deprecated", because dbms_scheduler implicitly commits, correct ?


Tom Kyte
February 03, 2010 - 10:15 am UTC

I cannot say anything other than I would be very surprised to see it go away and I'll be filing a bug immediately when and if I ever receive a beta that doesn't have it...

great

Sokrates, February 03, 2010 - 10:16 am UTC

thank you

dbms_job not vs dbms_scheduler

Marat Tolgambayev, February 03, 2010 - 6:37 pm UTC

Thanks again, Tom!

You are right, and I re-thought about my application design. As soon as you are sure that DBMS_JOB stays forever (or it's equivalent, without implicit commits), now I don't oppose dbms_job to dbms_scheduler. So, I've rebuild my application and use dbms_job.

Thank you!

Ken, August 18, 2010 - 11:01 am UTC

Just as an curiosity , what technology behind DBMS_scheduler, is it using AQ?
Tom Kyte
August 19, 2010 - 1:54 am UTC

no, it isn't a message in, message out thing - it has repeating schedules and such. It isn't a messaging paradigm at all. it is more like a calendar than anything else.

A reader, August 19, 2010 - 8:14 am UTC

Thanks Tom.

A reader, January 11, 2012 - 11:19 am UTC

How to set the "retry count" / disable retry of the scheduler jobs ? I am in 11g R2
Tom Kyte
January 11, 2012 - 3:25 pm UTC

You won't catch me writing this often:


don't let the job fail - catch "any exception" with when others - send yourself an email/record the failure.

The job queue will think the job succeeded, you'll know otherwise.

database identifier as part of dbms_scheduler_notifications

Jess, October 11, 2018 - 11:09 am UTC

Hello masters,

If you're defining a job in dba_scheduler_jobs (well, a program, but that shouldn't matter) and a notification to go with it, how can you include something in the notification that uniquely identifies the database (dynamically!) that generated the notification?

The default body/subject variables ( https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72280 ) don't include anything like that. If you have the same job running in multiple environments, how do you communicate to the recipient in which environment the failure took place (dynamically, in a way that doesn't require hardcoding)?

This is for 11.2.

Thank you in advance!
Chris Saxon
October 11, 2018 - 2:46 pm UTC

You can get the database id and name from v$database:

select dbid, name 
from   v$database;


Select these into a variable which forms part of your subject and/or body.

Jess, October 11, 2018 - 4:26 pm UTC

Hi Chris,

I know how to get the db id. What I don't know is how to define a new/custom variable, put that info into it, and get that variable used in the subject for the notification. Could you please provide and example or point me to where this is on the Oracle docs?

ADD_JOB_EMAIL_NOTIFICATION writeup on the page I mentioned above lists stock variables I can use, but I can't find anything on how I can define new ones...

Many thanks in advance for your help.

Jess, October 12, 2018 - 9:00 am UTC

Hi Connor,
It's nudging in the right direction as a bare minimum, but isn't what we'd need ideally....

We have a view over all_services that defined to select differently in different environments, which gives people the names they're familiar with (as environments rotate, etc.). Ideally, we'd like to be able to select from that view and have the result in the subject or body...

Is there really no way to define custom variables?

Thanks again!

Connor McDonald
October 13, 2018 - 4:16 am UTC

Is there really no way to define custom variables?

Sorry, you're out of luck there.

Jess, October 12, 2018 - 12:30 pm UTC

More importantly, however, doing it like that will cause variables to be evaluated when the subject is defined. That is, if one database is cloned from another one, these values will be incorrect.

The thing we're after is defining variables that could evaluate dynamically, so that subject (or body) definition is the same (and generic) in every environment, but the value gets populated when the notification is sent....

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