RE: DBMS_JOB - BROKEN FLAG ISSUE

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_at_kanbay.com
: POWERING TRANSFORMATION -----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alberto Dell'Era Sent: Saturday, December 16, 2006 6:52 AM To: Nagarajan.Subbiah_at_aetn.com
Cc: oracle-l_at_freelists.org
Subject: Re: DBMS_JOB - BROKEN FLAG ISSUE

On 12/16/06, Subbiah, Nagarajan <Nagarajan.Subbiah_at_aetn.com> 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_at_ORACLE10> variable j number
dellera_at_ORACLE10> exec dbms_job.submit (:j, what=>'begin null; end;', interval=>'sysdate+1');

PL/SQL procedure successfully completed.

dellera_at_ORACLE10> commit;

Commit complete.

dellera_at_ORACLE10> select broken from user_jobs;

BRO

---
N

dellera_at_ORACLE10> exec dbms_job.broken (:j, true);

PL/SQL procedure successfully completed.

dellera_at_ORACLE10> select broken from user_jobs;

BRO
---
Y

dellera_at_ORACLE10> commit;

Commit complete.

dellera_at_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"
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l