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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Hariharan.

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

Answered by: Connor McDonald - Last updated: July 25, 2019 - 2:55 am UTC

Category: Database Administration - Version: 12.1.0.2

Viewed 1000+ times

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


and you rated our response

  (3 ratings)

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

Reviews

Thanks,

August 07, 2017 - 11:39 pm UTC

Reviewer: Hariharan Senthil pandiyan

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

Thanks,

Connor McDonald

Followup  

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,

August 16, 2017 - 9:28 am UTC

Reviewer: Hariharan Senthil pandiyan

killed job works fine. Thanks.
Connor McDonald

Followup  

August 16, 2017 - 12:57 pm UTC

Glad it worked out for you

How to stop multiple job using single job

July 10, 2019 - 6:04 am UTC

Reviewer: Samanwaya from INDIA

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

Followup  

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;


More to Explore

Scheduler

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