Home » SQL & PL/SQL » SQL & PL/SQL » Automatic abropting the long running job (Oracle 12c)
Automatic abropting the long running job [message #676680] Wed, 03 July 2019 11:33 Go to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Dear All,


We have a scheduler which runs with inteval of 5 minutes. But sometimes it was running for a week .So is there any way to abropt the job automatically after it runs 30 minutes. I have searched and got max_run_duration attribute and set to 30 minutes however the job had not been stopped automatically.

Please help .



Re: Automatic abropting the long running job [message #676681 is a reply to message #676680] Wed, 03 July 2019 11:39 Go to previous messageGo to next message
BlackSwan
Messages: 26625
Registered: January 2009
Location: SoCal
Senior Member
sss111ind wrote on Wed, 03 July 2019 09:33
Dear All,


Please help .



Only after you learn to follow Posting Guidelines.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


Re: Automatic abropting the long running job [message #676691 is a reply to message #676681] Thu, 04 July 2019 04:49 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
Given the nature of the question I'm really not sure what you are complaining about.

@sss111ind - a quick look at the documentation suggests that max_run_duration does not cause jobs to stop automatically. Instead it causes an event to be raised and the event handler then needs to do something with it.
Have a read of: https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse008.htm#ADMIN12448
Re: Automatic abropting the long running job [message #676692 is a reply to message #676691] Thu, 04 July 2019 08:42 Go to previous messageGo to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Thanks cookiemonster

1.Is there any way to abrupt the job if it runs infinitely.
2.And how to handle the event it raised. I did not find any example.

Is this a process to handle the scenario.
When event raised then it should call a user defined procedure where we have to stop the job forcefully(dbms_scheduler.stop_job) .


Re: Automatic abropting the long running job [message #676693 is a reply to message #676692] Thu, 04 July 2019 11:44 Go to previous messageGo to next message
John Watson
Messages: 8051
Registered: January 2010
Location: Global Village
Senior Member
If you assign the job to a window rather than to a schedule, you can set the stop_on_window_close attribute.
Re: Automatic abropting the long running job [message #676695 is a reply to message #676693] Fri, 05 July 2019 09:47 Go to previous messageGo to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

I dont know it is the correct way or it would work.

--CREATE ORIGINAL JOB
BEGIN
    dbms_scheduler.create_job('DATA_PULL_JOB', 
    job_type => 'PLSQL_BLOCK', 
    job_action => 'BEGIN TEST(); 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 => false, 
    auto_drop => true,
    comments => NULL);

    dbms_scheduler.set_attribute('DATA_PULL_JOB', 'max_run_duration', '+000 00:10:00');
    dbms_scheduler.enable('DATA_PULL_JOB');
    COMMIT;
END;


--ADDING THE EVENT TO STOP THE JOB FORCEFULLY
BEGIN
    dbms_scheduler.add_event_queue_subscriber('DATA_EVENT');
END;

BEGIN
    dbms_scheduler.set_attribute('DATA_PULL_STOP_JOB', 'job_action', 'BEGIN DBMS_SCHEDULER.stop_job(''DATA_PULL_JOB'',TRUE) END;'
    );
END;

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

Re: Automatic abropting the long running job [message #676696 is a reply to message #676695] Fri, 05 July 2019 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 26625
Registered: January 2009
Location: SoCal
Senior Member
>I dont know it is the correct way or it would work.
Why don't you know?
What did you observe when code was actually tested?
Re: Automatic abropting the long running job [message #676697 is a reply to message #676696] Sat, 06 July 2019 03:06 Go to previous messageGo to next message
sss111ind
Messages: 608
Registered: April 2012
Location: India
Senior Member

Yes its working nicely. Here is the test case.Thanks a lot to all and asktom team.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9536221800346281378


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;
JOB_NAME                STATUS          REQ_START_DATE
-------------          --------------   ------------------
DATA_PULL_STOP_JOB	SUCCEEDED	06-JUL-19 01.21.55.000000000 PM ASIA/CALCUTTA
DATA_PULL_JOB	        STOPPED	        06-JUL-19 01.17.00.000000000 PM +05:30
DATA_PULL_STOP_JOB	SUCCEEDED	06-JUL-19 01.18.37.000000000 PM ASIA/CALCUTTA
DATA_PULL_JOB	        STOPPED	        06-JUL-19 01.13.00.000000000 PM +05:30
DATA_PULL_STOP_JOB	SUCCEEDED	06-JUL-19 01.15.19.000000000 PM ASIA/CALCUTTA
DATA_PULL_JOB	        STOPPED	        06-JUL-19 01.11.00.000000000 PM +05:30
DATA_PULL_STOP_JOB	SUCCEEDED	06-JUL-19 01.12.01.000000000 PM ASIA/CALCUTTA
DATA_PULL_JOB	        STOPPED         06-JUL-19 01.09.00.000000000 PM +05:30



Now the question is can we stop multiple job using a single job ?

[Updated on: Sat, 06 July 2019 03:48]

Report message to a moderator

Re: Automatic abropting the long running job [message #676698 is a reply to message #676697] Sat, 06 July 2019 07:28 Go to previous message
BlackSwan
Messages: 26625
Registered: January 2009
Location: SoCal
Senior Member
Alternatively, you could modify the original job code so that it simply terminates as expected & no additional code is required.

I suspect original job contains EXCEPTION WHEN OTHERS existing inside some LOOP that silently ignores an error & results in infinite LOOP
Previous Topic: How to Create variable out of Table Type Variable!
Next Topic: Trying to Report on Date Periods within a table
Goto Forum:
  


Current Time: Sat Nov 16 18:47:04 CST 2019