Home » SQL & PL/SQL » SQL & PL/SQL » dbms_job slight bug? (Oracle 9.2)
dbms_job slight bug? [message #398233] Wed, 15 April 2009 13:26 Go to next message
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 #398241 is a reply to message #398233] Wed, 15 April 2009 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you don't post what you did and we can reproduce I tend to trust Oracle and think you did something wrong.

So post a full working test case and also your Oracle version with 4 decimals.

Regards
Michel
Re: dbms_job slight bug? [message #398254 is a reply to message #398241] Wed, 15 April 2009 14:34 Go to previous messageGo to next message
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 #398255 is a reply to message #398233] Wed, 15 April 2009 14:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>--so 4 and 1 do not get in wrong order
Rows in a table have NO inherent order.
Rows in a table are like balls in a basket.
Which ball is the first ball in the basket?
Re: dbms_job slight bug? [message #398256 is a reply to message #398255] Wed, 15 April 2009 14:45 Go to previous messageGo to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
OK.
I think most everyone understood.

There is a date column. The select orders by date. The same date would ruin the select by order by 1 desc.

[Updated on: Wed, 15 April 2009 14:47]

Report message to a moderator

Re: dbms_job slight bug? [message #398262 is a reply to message #398233] Wed, 15 April 2009 16:30 Go to previous messageGo to next message
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 #398490 is a reply to message #398262] Thu, 16 April 2009 08:03 Go to previous messageGo to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
I believe DBMS_SCHEDULER started in Oracle 10g.

It's not really failure but an interval shorter than execution time.


Re: dbms_job slight bug? [message #398584 is a reply to message #398490] Thu, 16 April 2009 17:13 Go to previous messageGo to next message
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;

Re: dbms_job slight bug? [message #399075 is a reply to message #398584] Mon, 20 April 2009 08:02 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you look at the documentation for DBMS_JOB, it states quite clearly that this will happen.
Previous Topic: Export -Import
Next Topic: versioning
Goto Forum:
  


Current Time: Mon Nov 11 01:57:58 CST 2024