Oracle: How To Detect Client Process Termination Like It Works For Sqlplus?

I have the following problem in my application which connects to an Oracle 10g database: When my client crashes, or the process is terminated via task manager, or the client loses

Solution 1:

SQL*Plus uses OCI - Oracle Call Interface. This is an extensive API (over four hundred function calls. OCI provides a lot of methods for handling connections and sessions. I dont know for sure but I would guess that SQL*Plus uses OCILogon2() to register a dedicated session for the user, and puts something in the session handle which allows it to detect when the session ABENDs.

So, probably your application isn't using OCI to connect the database. If you want to know more about OCI you can read the docs. Be warned it's pretty low level stuff! Find out more.

Solution 2:

You can set sqlnet.expire_time=minutes_to_check in $ORACLE_HOME/network/admin/sqlnet.ora; it won't be instantaneous like oci, but it will clean up dead connections.

Solution 3:

Further investigation showed that although sqlplus does behave differently when killing the process, in fact it behaves the same way when the network cable is plugged out for a second => The dangling session will stay on the server.

I have now created a user profile with a limited idle_time which is assigned to the users I need. A background thread sends "keepalive" queries to keep the connection from being SNIPED by PMON.

See my other question Oracle: idle_time appears to be ignored if you are interested in what I did and see the answer on what you have to do to make idle_time work.