Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to use execute immediate with session killing?

Re: how to use execute immediate with session killing?

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Fri, 14 Feb 2003 09:25:31 +0100
Message-ID: <b2i97u$1d5d14$1@ID-152732.news.dfncis.de>


Hi, Ryan,

"Ryan" <rgaffuri_at_cox.net> schrieb im Newsbeitrag news:TaU2a.1901$4F3.50599_at_news2.east.cox.net...
>
> "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
> news:b2godt$1cb25g$1_at_ID-152732.news.dfncis.de...
> > Hi !
> >
> > If you are talking about DBMS_JOBS, then I regret to say that killing
> > session of those job
> > is not possible in the normal way by KILL SESSION. The only ways to stop
a
> > job are
> >
> > DBMS_JOB.REMOVE( )
> > (job owner user can do it)
> > or
> > set the job to 'broken' (even if it runs fine, but it will not be
started
> in
> > that case).
> > (job owner user can do it)
> > or
> > ALTER SYSTEM set job_queue_processes = 0
> > (that is: stop *all* jobs, only DBAs should be doing that.)
> >
> > The reason for it is that the job session is not a normal user session
by
> > login, it is created by
> > an Oracle background process. The session "username" is USER, having no
> > terminal in v$session,
> > and being permanently active.
> > Anf even if You succeded to kill it, it will be re-opened by Oracle
> withing
> > job_queue_interval automatically.
> >
> > hth
> >
> > Jan.
> >
> > "Ryan Gaffuri" <rgaffuri_at_cox.net> schrieb im Newsbeitrag
> > news:1efdad5b.0302131021.3bef58bb_at_posting.google.com...
> > > this is dying on me. is there a way to do this dynamically?
> > >
> > > EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '':1,:2''';
> > > USING v_sid, v_serial#;
> > >
> > > I have a requirement to allow users to be able to kill a job that is
> > > running. only way to do that is to kill the session. So Im giving them
> > > a wrapper.
> >
> >
>
> sorry I re-read your post. so in order to kill a job that is in progress.
I
> have to set it to broken right? I can then re-set it to Broken = 'N'
> immediately? this will reset it to fire at its regular interval?
>

AFAIK, yes. If I am wrong, please somebody correct me.

hth Jan Received on Fri Feb 14 2003 - 02:25:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US