Killing job after sometime is not working. [message #617586] |
Wed, 02 July 2014 01:43 |
|
ramya_162
Messages: 107 Registered: August 2013 Location: Banglore
|
Senior Member |
|
|
Hi Experts,
I have created master job that is dynamically creating child jobs
and once the child job is completing it will be doping automatically.
Now I have set timeout for each child job.
If any child job is running for more than 5 minutes it should be dropped.
Timeout is not working properly even the job is running for more than 5 minutes.
The master job runs every hour.
The child job should be dropped automatically if it's running for more than 5 minutes.
In the next run of master job or child job I should not get any errors.
CREATE OR REPLACE PROCEDURE DYNAMIC_JOB_SCHEDULER
IS
BEGIN
FOR CUR_SCH IN
(SELECT sl.OWNER_NAME,sl.BU_CODE,sl.SCH_ID
FROM SCHEMA_LIST sl,
SCHEMA_CONFIGURATION SC
WHERE sl.SCH_ID = SC.SCH_ID
AND SC.ACTIVE = 'Y')
LOOP
BEGIN
dbms_scheduler.create_job (
job_name => 'DYN_JOB'||CUR_SCH.OWNER_NAME,
job_class => CUR_SCH.OWNER_NAME,
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DAILY_SALES_PKG.SALE_REV('||CUR_SCH.SCH_ID||'); END;',
auto_drop => TRUE,
comments => 'Fetching job for '||CUR_SCH.OWNER_NAME,
enabled => TRUE
);
DBMS_SCHEDULER.SET_ATTRIBUTE(name => 'DYN_JOB'||CUR_SCH.OWNER_NAME
,attribute => 'max_run_duration'
,ACTIVE => TO_DSINTERVAL('0 00:05:00'));
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END DYNAMIC_JOB_SCHEDULER;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'DYN_MASTER_JOB'
,start_date => SYSTIMESTAMP
,repeat_interval => 'FREQ=HOURLY;BYMINUTE=0; BYSECOND=0;'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'DYNAMIC_JOB_SCHEDULER'
,comments => 'master job runs every hour'
,enabled => TRUE
);
END;
begin
dbms_scheduler.add_event_queue_subscriber('myagent');
end;
/
create or replace procedure sniper_proc (message IN sys.scheduler$_event_info) as
v_autodrop varchar2(30);
begin
if message.event_type != 'JOB_OVER_MAX_DUR' then
raise PROGRAM_ERROR;
end if;
select auto_drop into v_autodrop from dba_scheduler_jobs where job_name=message.object_name and owner=message.object_owner;
if (v_autodrop='TRUE') then
dbms_scheduler.drop_job('"'||message.object_owner||'"."'||message.object_name ||'"',force=>TRUE);
else
dbms_scheduler.stop_job('"'||message.object_owner||'"."'||message.object_name ||'"',force=>TRUE);
end if;
end;
/
begin
dbms_scheduler.create_program (
program_name => 'sniper_prog',
program_action=> 'sniper_proc',
program_type => 'stored_procedure',
number_of_arguments => 1,
enabled => FALSE) ;
dbms_scheduler.define_metadata_argument ( 'sniper_prog','event_message',1);
dbms_scheduler.enable('sniper_prog');
end;
/
begin
dbms_scheduler.create_job('sniper_job',
program_name=>'sniper_prog',
event_condition =>
'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
queue_spec =>'sys.scheduler$_event_queue,myagent',
enabled=>true);
end;
/
Please help me.
Thanks.
|
|
|
|
|
|
Re: Killing job after sometime is not working. [message #617593 is a reply to message #617589] |
Wed, 02 July 2014 02:11 |
John Watson
Messages: 8929 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
ramya_162 wrote on Wed, 02 July 2014 08:01HI,
Could you please help me how to resolve the issue in my approach.
Thanks. No. I think you are doing this the wrong way. It looks like the sort of code that people used to write last century, in the days of DBMS_JOB.
|
|
|
|
Re: Killing job after sometime is not working. [message #617620 is a reply to message #617614] |
Wed, 02 July 2014 05:03 |
John Watson
Messages: 8929 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
ramya_162 wrote on Wed, 02 July 2014 10:51Hi,
Can you please provide the code for my requirement with new approcah.
STOP_ON_WINDOW_CLOSE attribute set to TRUE.
Thanks. Unless you want to pay for consultancy, you will need to do some work yourself.
|
|
|