Oracle: How to detect client process termination like it works for sqlplus?

Question

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)?

I have encountered such a difference in behaviour when using a "thin" oracle client driver as opposed to an "OCI" driver. — Juergen Hartelt, Mar 09 '12 at 17:56

score 2 · Answer 1 · answered Mar 09 '12 at 13:59

2

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.

answered Mar 09 '12 at 13:59

APC

144,005
19
170
281

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 09 '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 09 '12 at 14:31

score 1 · Answer 2 · answered Mar 09 '12 at 19:39

1

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.

answered Mar 09 '12 at 19:39

Adam Musch

13,286
2
28
32

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 08: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

score 1 · Accepted Answer · edited May 23 '17 at 10:24

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.

Oracle: How to detect client process termination like it works for sqlplus?

3 Answers3

Linked