Re: Killed session not terminating?

Jeremy wrote:
> In article <1117094950.130452.240190_at_f14g2000cwb.googlegroups.com>,
> says...
> >
> > Regarding the client receiving the "session killed" message, I don't
> > believe this occurs until the client next attempts to do something. So
> > if the client is idle then a row remains in V$SESSION with a status of
> > "KILLED", as you observe. Its only when the client next attempts some
> > action that it receives the "your session has been killed" message
> > (ORA-00028 I think).
> >
>
> My experience is that as soon as the session is killed in Oracle (either
> via OEM or e.g. a kill -9) then sqlplus immediately responds.

Please try using other signals like -1 (hangup), -2 interrupt, -3 (quit) and -15 (terminate) before bringing down the hammer -9. The other signals give the processes the chance to flush buffers and release resources. I even try a -4 (Illegal instruction) before using the -9.
>
> > As far as the locks being held then maybe PMON is unable to tidy up
> > properly? It might be worth seeing if there are any trace files
> > produced by PMON indicating a problem.
>
> Well... found the problem (application / data issue) but just for good
> measure I will document it here.
>
> We have a pl/sql proc (1)
> - which calls a java stored proc (2)
> - which in turn then calls a shell script (3)
> - which then calls an executable (4) on the server (solaris 9).
>
> Owing to a data error (or should I say something was null and it was not
> catered for) the call that got made to the executable (4) was malformed
> and it appears that this resulted in a "hang" and this was all outside
> of Oracle. As far as Oracle is concerned, it is awaiting a response
> from the shell script (3) which it never receives...
>
> I don't know if this makes sense or helps anyone but I thought it might
> be worth documenting my experience here (not least so that when
> something similar recurs and I head straight for google I'll be able to
> find this!!).
>
>
> cheers to all who offered suggestions
>
> --
>
> jeremy

And thank you for closing the loop with your root cause to the problem. It is so much niceer than those threads that trail off with out decisive conclusions.

Have a great day.

    Ed Received on Thu May 26 2005 - 11:36:39 CDT