Re: Killed session not terminating?

Jeremy wrote:
> Hi folks, can anoyone help with this question?
>
> Platform is oracle 9iR2 on Solaris 9 with a XP Pro client running 9iR2
> client software.
>
> On the client (sqlplus) I started execution of a PL/SQL packaged
> procedure. Now for some reason this is not completing - i don't know if
> it is looping 9don't think so) or some other factor is preventing it
> from completing.
>
> Anyway, having observed that this had "hung" I used OEM to kill the
> server session (yes I know I should simply kill the client session but
> there are other reasons for taking this approach in this case, which, if
> you're interested, I will go into later).
>
> The session shows in OEM as "KILLED" but still holds a number of locks
> and won't go away. Also I would expect a disconnect of the client to be
> forced with a message such as "your session has been killed" (cannot
> remember the precise wording but I am sure you know the sqlplus message
> I refer to) plus an ORA-3114 not connected to oracle. But the client
> reports nothing and appears to be unaware that the server proces is
> killed.
>
> So if the client has not had its server session killed (which I assume
> is in fact the case), what could cause this - OEM still shows the
> session but with status KILLED.
>
> Appreciate any hints as this is rather perplexing!
>
> --
>
> jeremy

Hi Jeremy,

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).

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.

Hope that's of some help.

Regards,

Tim Kearsley
HBS Milton Keynes Received on Thu May 26 2005 - 03:09:10 CDT