Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Stop a job via SQL/Stored procedure
Well, this won't stop a job that's already running - it will only prevent it from
starting. To stop a running job, corresponding session should be killed, which
is not that trivial to do - you gotta locate the session in v$session and then
alter system kill session '<v$session.sid> <v$session.serial#>' immediate
(of course, <v$session.xxx> should be replaced with actual values for these
columns). The most tricky part here is locating the session for the running job.
Of course, job status must be also set to broken before killing its session,
because Oracle will start new SNP process to replace killed one and this process
will immediately pick up the job again since it was interrupted. And note that
if you set broken flag while job is running, and it then completes successfully,
broken flag will be reset, so you really really need to kill running job before you
set its broken flag.
Corrections and additions welcome.
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "Sybrand Bakker" <postbus_at_sybrandb.demon-verwijderdit.nl> wrote in message news:ea2pmu4t0hl41v0ce0l3dumf6um672ol5f_at_4ax.com...Received on Wed Aug 28 2002 - 05:16:29 CDT
> On 28 Aug 2002 00:46:56 -0700, jabelsc_at_yahoo.com (Abel Suarez) wrote:
>
> >Hi everybody!
> >
> > Is there a way to stop/start an Oracle job via SQL or a stored
> >procedure? I need to do it through a java program. I'm using Oracle
> >8.1.6.
> >
> > THanks in advance.
> >
> > Abel S.
>
> dbms_job.broken(<id>,TRUE)
>
> simple is it not? Just read up the dbms_job doco.
>
> Hth
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address