Take the 2-minute tour ×

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 connection for a while, the appropriate entry in the v$session view remains.

Now when I connect to the database with sqlplus, and i kill sqlplus.exe through the task manager, the session entry gets deleted almost instantly.

Latter behaviour would be preferred for my application for various reasons.

What does sqlplus do differently, and can I do it as well in my own application(s)?

share|improve this question
I have encountered such a difference in behaviour when using a "thin" oracle client driver as opposed to an "OCI" driver. –  Juergen Hartelt Mar 9 '12 at 17:56

3 Answers 3

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.

share|improve this answer
I'm using Qt which offers the QSQLOCI plugin which is a Qt wrapper around OCI. I guess I'm gonna have a look into the QSQLOCI layer. –  Tim Meyer Mar 9 '12 at 14:05
That layer uses OCIServerAttach and OCISessionBegin; I couldn't find any occurrence of OCILogon or OCILogon2. Do you know if ServerAttach/SessionBegin is an alternative to OCILogon or are these unrelated? –  Tim Meyer Mar 9 '12 at 14:31

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.

share|improve this answer
Does that clean only dead connections? Or will it also affect connections which have been idle for longer than minutes_to_check? –  Tim Meyer Mar 12 '12 at 8:01
It does both actually. We have our systems set up this way, as it doesn't matter then about the capacity of the client app, and most of our Java applications use connection pooling so they can restart a dead connection in their pool. –  Adam Musch Mar 12 '12 at 15:19
Is there a way to set something like that for the current session only? The problem is that the target server is used for several applications (not only ours) and we must ensure other applications won't stop working when we want to change anything on the server –  Tim Meyer Mar 13 '12 at 13:59
Not that I'm aware of - you'd have to use the OCI layer like APC recommended. –  Adam Musch Mar 13 '12 at 21:51
up vote 1 down vote accepted

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.

share|improve this answer

Your Answer


