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.
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" <dmorgan_at_exesolutions.com> wrote in message
news:3D2473D9.7D16F9FB_at_exesolutions.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.
>
> 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