Re: Session does not terminate when a connection drops while running PLSQL.

jim rowe wrote:

> Oracle 8.1.7.0.0
> Platform NT.
>
> A client application is connected via net8 (TCP) to a database server.
> The client performs an update transaction which triggers a PL/SQL
> procedure which takes several minutes to run.
>
> While the PLSQL procedure is running the client application dies due
> to a catastrophic failure (e.g. a process failure or power failure on
> the client machine).
>
> On restarting the client and retrying the transaction we
> are finding that the original session is still active and
> still locking several resources. The session appears to
> be hung and never ends.
>
> The only way we have found to kill the original session is to shutdown
> and startup the database. (alter system kill session doesn't work.)
>
> Is this a known problem? How can we guarantee that the original
> session ends properly when the client application fails abnormally
> while the PLSQL procedure is running?
>
> Thanks,
> Jim.

It is a known behavior but I wouldn't call it a known problem. When you do ALTER SYSTEM KILL SESSION do you check in v_$session to see the session status or do you just assume it isn't killed?

In all likelihood the session has been killed and the time your are impatiently observing is the time required to undo the transactions and clean things up.

Daniel Morgan Received on Thu Jul 04 2002 - 11:12:47 CDT