RE: DBMS_JOB - BROKEN FLAG ISSUE
- From: "Richard J. Goulet" <rgoulet@xxxxxxxxxx>
- To: <alberto.dellera@xxxxxxxxx>, <Nagarajan.Subbiah@xxxxxxxx>
- Date: Mon, 18 Dec 2006 10:37:22 -0500
Alberto,
You are pretty darn close to being right as I've also run into
similar things. In my case I was trying to unset the broken flag, but
since the job ran every 10 minutes as fast as I could unbreak it, it
would break again. I believe the status is set by either the dbms_job
procedure or the last execution whichever terminates last.
Dick Goulet, Senior Oracle DBA
45 Bartlett St Marlborough, Ma 01752, USA
Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795
RGoulet@xxxxxxxxxx
: POWERING TRANSFORMATION
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Alberto Dell'Era
Sent: Saturday, December 16, 2006 6:52 AM
To: Nagarajan.Subbiah@xxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: DBMS_JOB - BROKEN FLAG ISSUE
On 12/16/06, Subbiah, Nagarajan <Nagarajan.Subbiah@xxxxxxxx> wrote:
> One of our developer has reported a problem that when the broken flag
> is set to TRUE from the application ( connection comes from the
> webserver ) for a scheduled job, its not being set. I tried the
> following from the SQLPlus
>
> dbms_job.broken(<jobno>,TRUE);
>
> The broken flag is set to 'Y'.
>
> Then I gave the 'commit;' After the commit, The broken flag was set as
> 'N'.
It doesn't reproduce on 10.2.0.2 on windows XP:
dellera@ORACLE10> variable j number
dellera@ORACLE10> exec dbms_job.submit (:j, what=>'begin null; end;',
interval=>'sysdate+1');
PL/SQL procedure successfully completed.
dellera@ORACLE10> commit;
Commit complete.
dellera@ORACLE10> select broken from user_jobs;
BRO
---
N
dellera@ORACLE10> exec dbms_job.broken (:j, true);
PL/SQL procedure successfully completed.
dellera@ORACLE10> select broken from user_jobs;
BRO
---
Y
dellera@ORACLE10> commit;
Commit complete.
dellera@ORACLE10> select broken from user_jobs;
BRO
---
Y
Could it be that you set the broken flag when the job was running, then
the job ended, tried to update the status row, found it locked by you,
and so only immediately after the commit, was able to update the status
row thus overwriting your broken value ?
BTW when you exit, sqlplus commits by default, so your scenarios are
identical - maybe the difference was in the job running or not.
--
Alberto Dell'Era
"Per aspera ad astra"
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts: