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

jimlorraine_at_hotmail.com (jim rowe) wrote in message news:<9a05ce47.0207040526.6041c2a4_at_posting.google.com>...
> 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.

Generally...It is not good practice to have a client wait for "Several minutes" for a database call to return. If the process (Insert, Update, Delete) is going to take a long time then it is better to break it up into two. First, client submits a request and saves it in e.g. a holding table and returns immediately. Then that request can be picked up by a background process which may take several minutes to run independent of whether the calling client is still connected or not.

HTH
//Rauf Sarwar Received on Thu Jul 04 2002 - 15:17:47 CDT