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

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 04 Jul 2002 19:17:42 +0200
Message-ID: <al09iukl992uu4ojj98kri0sospfubg72o@4ax.com>

On 4 Jul 2002 06:26:07 -0700, jimlorraine_at_hotmail.com (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.

- Avoid using Windows
- Avoid powersurges
- Enable dead connection detections (sqlnet.expire_time = 10 in

sqlnet.ora) on the server. Due to a limitation in NT4 though this will stop working with more than 16 connections. This is resolved in 9i and using WinXP professional, IIRC

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jul 04 2002 - 12:17:42 CDT