Skip to Main Content
  • Questions
  • Oracle Scheduler job continue to run though is set to stop after 240 minutes/4 hours

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Hariharan.

Asked: August 07, 2017 - 8:03 pm UTC

Last updated: December 18, 2023 - 1:51 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hi,

Although they had set the max job duration to 240 minutes/4 hours, the Oracle Scheduler job continue to run for more than 15 hours until they killed it. How could we get this job to stop once past the max job duration?

I had already set that via Oracle Scheduler on OEM. It's showing in the dba_scheduler_jobs but it doesn't stop the job. Hence, why it doesn't stop the job.

BEGIN
dbms_scheduler.create_job('"GATHERSTATS_OSMCORE_SCHEMA"',
job_type=>'EXECUTABLE', job_action=>
'c:\windows\system32\cmd.exe'
, number_of_arguments=>2,
start_date=>TO_TIMESTAMP_TZ('20-JUN-2017 12.00.00.000000000 AM AMERICA/NEW_YORK'
,'DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=
>
'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=12;BYMINUTE=0;BYSECOND=0'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>FALSE,comments=>
'Gatherstats_OSMCORE_Schema'
);
dbms_scheduler.set_attribute('"GATHERSTATS_OSMCORE_SCHEMA"','max_run_duration','
+000 04:00:00');
dbms_scheduler.set_attribute('"GATHERSTATS_OSMCORE_SCHEMA"','raise_events',36);
COMMIT;
END;


Thanks,

and Connor said...

From the docs:

"max_run_duration

This attribute specifies the maximum amount of time that the job should be allowed to run. Its datatype is INTERVAL DAY TO SECOND. If this attribute is set to a non-zero and non-NULL value, and job duration exceeds this value, the Scheduler raises an event of type JOB_OVER_MAX_DUR. It is then up to your event handler to decide whether or not to allow the job to continue."


So all we are doing is monitoring the job, and raising the job_over_max_dur event when it goes to long. You could create a job that responds to this event to take whatever action you'd like to do, eg

begin dbms_scheduler.add_event_queue_subscriber('myagent'); end;

begin
dbms_scheduler.create_job(
  'killer',
  job_action=>'...appropriate killing behaviour...',
  event_condition =>
  'tab.user_data.object_name = ''GATHERSTATS_OSMCORE_SCHEMA'' and
  tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
  queue_spec =>'sys.scheduler$_event_queue,myagent',
  enabled=>true);
end;


Rating

  (7 ratings)

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

Comments

Thanks,

Hariharan Senthil pandiyan, August 07, 2017 - 11:39 pm UTC

So this Job will Kill the job which exceeds the Limit ? Please explain.

Thanks,

Connor McDonald
August 08, 2017 - 11:45 pm UTC

Yes, what the second job does is entirely up to you.

It could

- page someone to say the first job is over its time
- find/kill the session of that running job

etc etc

Thanks,

Hariharan Senthil pandiyan, August 16, 2017 - 9:28 am UTC

killed job works fine. Thanks.
Connor McDonald
August 16, 2017 - 12:57 pm UTC

Glad it worked out for you

How to stop multiple job using single job

Samanwaya, July 10, 2019 - 6:04 am UTC

Thank you Asktom team

I have tested the killer job it is working as excepted. How can we stop multiple job using a single job ?

BEGIN
    --creating orignial job
    dbms_scheduler.create_job('DATA_PULL_JOB', 
    job_type => 'PLSQL_BLOCK', 
    job_action => 'BEGIN DBMS_lock.SLEEP(100000); END;',
    number_of_arguments => 0, 
    start_date => to_timestamp_tz('05-NOV-2018 06.17.03.356576000 PM +05:30', 'DD-MON-RRRR HH.MI.SSXFF AM TZR', 'NLS_DATE_LANGUAGE=english'),
    repeat_interval => 'freq=minutely; interval=2; bysecond=0;', 
    end_date => NULL, job_class => 'DEFAULT_JOB_CLASS',
    enabled => true, 
    auto_drop => true,
    comments => NULL
  );
    
   dbms_scheduler.set_attribute('DATA_PULL_JOB','max_run_duration','+000 00:03:00');
   dbms_scheduler.enable('DATA_PULL_JOB');
   
END;

 
 begin
  --adding event
    dbms_scheduler.add_event_queue_subscriber('DATA_EVENT');

    --adding another job to kill the above job
    dbms_scheduler.create_job('DATA_PULL_STOP_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN DBMS_SCHEDULER.stop_job(''DATA_PULL_JOB'',TRUE); END;',
    event_condition => 'tab.user_data.object_name = ''DATA_PULL_JOB'' and tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
    queue_spec => 'sys.scheduler$_event_queue,DATA_EVENT',
    enabled => true);
end;



Connor McDonald
July 25, 2019 - 2:55 am UTC

If its a fixed list, then your job action would be:

BEGIN 
  DBMS_SCHEDULER.stop_job(''JOB1'',TRUE); 
  DBMS_SCHEDULER.stop_job(''JOB2'',TRUE); 
  DBMS_SCHEDULER.stop_job(''JOB3'',TRUE); 
  ..
END;


or if it is something based on a criteria it could be:

BEGIN
 for i in (select job_name from user_scheduler_jobs where ... )
 loop
   DBMS_SCHEDULER.stop_job(i.job_name,TRUE); 
 end loop;
end;


max_run_duration

jorge, October 05, 2020 - 8:35 pm UTC

Can you let me know if the Maximum Run Duration is a Global setting or per job setting?
I see via OEM that max_run_duration is set to 60 minutes, can this value be change?

thanks

regards,
jorge

Connor McDonald
October 06, 2020 - 6:30 am UTC

You can set it for each job

Examples here

https://asktom.oracle.com/pls/apex/asktom.search?tag=oracle-scheduler-job-continue-to-run-though-is-set-to-stop-after-240-minutes4-hours

which also talks about what max_run_duration *actually* does

Thanks a lot

A reader, October 06, 2020 - 8:28 am UTC

Thanks a lot
Connor McDonald
October 07, 2020 - 2:20 am UTC

glad we could help

for Autonomous Database

mudit, August 17, 2023 - 3:16 am UTC

I was checking for the autonomous database and in autonomous database somehow the steps are not working, Can you please review the steps for autonomous database

A reader, December 13, 2023 - 4:04 pm UTC

If I have multiple jobs that might raise JOB_OVER_MAX_DUR event, is there a way to know which job triggered the event ? (in the procedure called by the job that listens to these events)


Connor McDonald
December 18, 2023 - 1:51 am UTC

You can create the second job to take as input the event message which is of type sys.scheduler$_event_info

eg

create or replace 
procedure long_job_handler(p_event IN sys.scheduler$_event_info) as
begin
    if p_event.event_type != 'JOB_OVER_MAX_DUR' then
      raise_application_error(-20000,'This should be only run when a job runs too long');
    end if;
   
    dbms_output.put_line('Owner='||  p_event.object_owner);
    dbms_output.put_line('Job='||  p_event.object_name);
    
    --
    --  any killing/emailing/etc you want to do
    --
end;
/

-- then use a program to pass the arguments

begin
    dbms_scheduler.create_program (
        program_name        => 'LONG_JOB_PGM',
        program_action      => 'LONG_JOB_HANDLER',
        program_type        => 'STORED_PROCEDURE',
        number_of_arguments => 1,
        enabled             => FALSE) ;

    dbms_scheduler.define_metadata_argument ( 'LONG_JOB_HANDLER','event_message',1);
    dbms_scheduler.enable('LONG_JOB_HANDLER');
end;
/

-

More to Explore

Scheduler

All of the database utilities including the Scheduler are explained in the Utilities guide.