Re: Crashed client application leaves Oracle connection behind

André Hartmann wrote:
> Hello everyone,
>
> I would like to describe a phenomenon which we get occasionally with
> one of our applications and see whether people have observed similar
> before or have an idea as to how to approach this problem in terms of
> analysis, debugging or so...
>
> The application is done in Visual C++ (MS Visual Studio 2003) and it
> acts as an Oracle client application. We currently use the Oracle 9
> client and server software on Windows 2000 and XP and we are about to
> shift to Oracle 10. But the phenomenon that I am describing occurs under
> Oracle 9 and Oracle 10 and all supported OSs. We use the Oracle Call
> Interface OCI to communicate with the database.
>
> Our application will open a database connection and process some
> transactions there. At some stage the application may crash. That is
> unpleasant but that's not the issue I want to write about here. We are
> working on the crash also and hope to eliminate it some day soon.
> However what we observe when such a crash occurs (in the middle of a
> transaction) is, that the application window will go, the typical
> Microsoft report dialogue will show up and the application process is
> not visible anymore in the task manager (not in the applications tab amd
> also not in the processes tab).
>
> So far so good, that's all as expected. However sometimes the Oracle
> server will still have the connection to the (already vanished)
> application in its list of sessions. We observe this using the Oracle
> Enterprise Manager. This does not happen with EVERY such crash but only
> once in a while. Typically when killing our application intentionally
> via the task manager or a kill tool, no Oracle session will be left
> over. Only when a crash occurs and even then only sometimes we will
> experience that.
>
> The session will go after a while, like half an hour. Then it wont be
> visible via the OEM anymore and locks and other resources that the
> transaction claimed are released by Oracle.
>
> Users reported to us that this scenario is especially nasty because
> when they experience the crash and they restart our application, they
> cannot resume their work because "work" would include imposing locks on
> resources that the orphaned session still holds...
>
> Users also reported that it helps to log off / log on their Windows
> work stations, which would eradicate the session. We couldnt reproduce
> this behaviour here in our developing environment. We get the crash and
> the orphaned session but cannot mend it by log off / log on. That's a
> bit strange because so far the problem sounded to me like a server-side
> issue... but I am not sure anymore.
>
> Thanks very much in advance for your input,
>
> André
> Berlin, Germany
> :)

SELECT schemaname, status FROM gv$session;

What does Oracle see? Likely the behavior is exactly as documented.

Go to the documentation at http://tahiti.oracle.com and look up "SQLNET" and "TIMEOUT."

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org