|
|
|
|
|
Re: Automatic abropting the long running job [message #676695 is a reply to message #676693] |
Fri, 05 July 2019 09:47 |
|
sss111ind
Messages: 636 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 #676697 is a reply to message #676696] |
Sat, 06 July 2019 03:06 |
|
sss111ind
Messages: 636 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 |
|
BlackSwan
Messages: 26766 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
|
|
|