Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Session does not terminate when a connection drops while running PLSQL.

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

From: Jon Waterhouse <>
Date: Thu, 4 Jul 2002 15:35:04 -0230
Message-ID: <3d248ecc$>

A couple of weeks ago (running Oracle 9i on NT) I killed a session which was about 20 seconds into a procedure that I realised had errors. That was about noon. I left it running overnight because the session still hadn't cleaned up and left. The next morning it was still there, so I shut down and restarted. Oracle had basically been using 100% of the computer's resources for the whole time. I don't know what it was up to, but whatever it was it wasn't making much progress at it....

"Daniel Morgan" <> wrote in message
> jim rowe wrote:
> > Oracle
> > 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 - 13:05:04 CDT

Original text of this message