dbms_job slight bug? [message #398233] |
Wed, 15 April 2009 13:26 |
goo321
Messages: 28 Registered: June 2008 Location: houston
|
Junior Member |
|
|
I have job with an interval of 5 hours (runs every 5 hours) but the job routinely takes longer than 5 hours.
This screws up breaking the job to prevent restart of package. The job automatically gets restarted despite being broken and despite the next date being set to 2 years in the future. The easy solution would be to remove the job. Anyone have any ideas of any other way?
Thanks very much.
I recreated this situation with a test job that takes 15 seconds with the interval of restarting set to 10 seconds.
on Oracle 9.
|
|
|
|
Re: dbms_job slight bug? [message #398254 is a reply to message #398241] |
Wed, 15 April 2009 14:34 |
goo321
Messages: 28 Registered: June 2008 Location: houston
|
Junior Member |
|
|
It did not occur to me anyone would run code and recreate the scenario.
create table zz_templog (log_date date default sysdate, text varchar2(30));
CREATE OR REPLACE PROCEDURE MONITOR.testkk IS
date1 date :=sysdate; --to see which procedure running
BEGIN
insert into zz_templog (text) values ('1 '||date1);commit;
sys.dbms_lock.sleep(7);
insert into zz_templog (text) values ('2 '||date1);commit;
sys.dbms_lock.sleep(7);
insert into zz_templog (text) values ('3 '||date1);commit;
sys.dbms_lock.sleep(7);
insert into zz_templog (text) values ('4 '||date1);commit;
sys.dbms_lock.sleep(1); --so 4 and 1 do not get in wrong order
END testkk;
/
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => x
,what => 'testkk();'
,next_date => to_date('15/04/2009 10:06:29','dd/mm/yyyy hh24:mi:ss')
,interval => 'SYSDATE+11/24/60/60' -- every 11 seconds seconds
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
After running previous code the job should be constantly inserting into log table. I assume the procedure should run a few times to build some backup into a queue somewhere. In other words wait 1 or 2 minutes to make sure this buggy situation appears.
select * from zz_templog order by 1 desc
If you attempt to break the job while the job is running, the dbms_job will automatically restart.
EXEC DBMS_JOB.BROKEN( 136312,TRUE); --substitute number from output above
commit;
The easy solution is
exec DBMS_JOB.remove(136311);
commit;
I was wondering if possible to stop execution with removing.
Thanks,
Karthik K
I did try other things.
-- setting longer interval works for second instance of running, Will restart 1 more time although broken, second restart is set for 2 weeks later.
exec dbms_job.interval(136310,'to_char(sysdate+14)');
commit;
--setting next day did not work
P.S.
I am a human being. Oracle is a faceless entity.
|
|
|
|
|
Re: dbms_job slight bug? [message #398262 is a reply to message #398233] |
Wed, 15 April 2009 16:30 |
coleing
Messages: 213 Registered: February 2008
|
Senior Member |
|
|
this is an inherent feature of DBMS_JOB. It will automatically restart upon failure. That is - failure of the plsql procedure.
The easiest way to prevent a restart is to ensure the plsql always succeeds (ie. completes normally with appropriate exception handling for error).
As you are on database 9i, you should consider upgrading to use DBMS_SCHEDULER even for single jobs.
|
|
|
|
Re: dbms_job slight bug? [message #398584 is a reply to message #398490] |
Thu, 16 April 2009 17:13 |
goo321
Messages: 28 Registered: June 2008 Location: houston
|
Junior Member |
|
|
Maybe this is a problem of trying not to change the requirements, but solved the problem by changing what
exec dbms_job.what(136313,'null;');
commit;
|
|
|
|