Re: canceling statement

"Steffen Ramlow" <s.ramlow_at_gmx.de> wrote in message news:<9hc3s6$d5je2$2_at_ID-54600.news.dfncis.de>...
> i wanna know, how it can be done programmatically
>
> eg.
>
> client starts query in thread 1
> thread 2 cancels the query (in the database!) after a timeout
>
>
> "Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3B395939.CD5C9D57_at_exesolutions.com...
> > Alex Filonov wrote:
> >
> > > Steffen Ramlow wrote:
> > >
> > > > how can i cancel a running statement?
> > >
> > > In sqlplus, CTRL-C. In Forms, look up key help.
> >
> > Control C may or may not kill a server process.
> >
> > On NT orakill.exe and on unix kill -9 if you want to be quick and
> > deadly.
> >
> > Daniel A. Morgan
> >

Steffen,

You can use dynamic SQL to issue the following directive:

ALTER SYSTEM KILL SESSION 'sid,serial#' e.g.

EXECUTE IMMEDATE 'ALTER SYSTEM KILL SESSION ''25, 500''' or some such, where sid is an integer representing the sessions sid, and serial# is an integer representing the sessions serial#. You need to get this information from the process that you might want to kill before you tie it up; so you'll want to have it report back it's sid and serial# before it goes off to do the heavy work, as follows:

SELECT
  si.sid,
  s.serial#
FROM
  V$SESSION S,
  V$PROCESS P,
  sys.V_$SESS_IO si
WHERE
  S.paddr = P.addr and
  si.sid(+)=s.sid and
  s.sid = (SELECT DISTINCT SID FROM V$MYSTAT);

You'll need the requisite privileges to kill a session and to select from the v$mystat view. You'll need to reconnect to the database to continue processing.

There is also an ALTER SYSTEM DISCONNECT SESSION 'sid, serial#' IMMEDIATE;, which I suspect will rollback the current transaction(?) This might be a more graceful approach.

Dan Received on Wed Jun 27 2001 - 09:55:52 CDT