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

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, 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

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,

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?

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).

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

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?

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,

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

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>

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.

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

Hi Tom,

Can you please respond to the question posed.

Thanks

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

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

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?

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.

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

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

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

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.

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,

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,

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,

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

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

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.

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.

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.

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

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

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.

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.

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.

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;
/

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 ?

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 ,

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

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.

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

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

Thanks a lot , Tom .

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

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!

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!

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).

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

thank you

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!

Just as an curiosity , what technology behind DBMS_scheduler, is it using AQ?

Thanks Tom.

How to set the "retry count" / disable retry of the scheduler jobs ? I am in 11g R2

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!

select dbid, name 
from   v$database;

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.

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!

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....