Unanswered: Killed sessions still there, so max number exceeded
Hi, we are facing a serious problem with ghost (or zombie, if you prefere) sessions with Oracle 9i. It seems that when the client application process aborts, the sessions are not closed automatically. Moreover, issuing the ALTER SYSTEM KILL SESSION command just "marks" the session as killed, but does not actually remove the session from the main list in the Enterprise Manager.
The problem is that after a while, I get an ORA-00018 (max number of sessions exceeded) even if I only have 12 sessions alive, and that's because I also have 140 sessions killed-zombie-ghost sessions.
Could someone please help me in gettind a rid of these sessions ? We are NOT using MTS, but our client application is using the ConnectionPoll object included in the OCCI (Oracle C++ Call Interface) library.
Thanks in advance.
Unfortunately I have to kill sessions programmatically. Moreover, since I use the OCCI connectionPool, multiple sessions are handled by the same Oracle process, so killing the process is NOT a choice...
BTW, by removing the OCCI connectionPool and creating connections directly it seems that ghost sessions disapper automatically after one minute, so I guess the connectionPool is doing something wring (it wouldn't surprise me, since OCCI has been reported to be a very buggy library... :-).
I thing we will throw away the OCCI connectionPool and setup MTS server-side.
You don't say which OS you have, but if Solaris then setup tcp_keepalive_interval parameter. It specifies the interval in milliseconds between keepalive packets sent by Solaris for each open TCP connection. This can be used to remove connections to clients that have become disconnected from the network.
# ndd -set /dev/tcp tcp_keepalive_interval 900000
You have to execute it as root, and the setting remains active till the next reboot. To make it permanent - put this command in the rc scripts, to have it executed during each boot of the machine. The command should be added in the /etc/init.d/inetinit file.
On Oracle side - edit the SQLNET.ORA in the oracle_home/network/admin, net80/admin or where the TNSADMIN points, either remove the SQLNET.EXPIRE_TIME or set is to some value like 30. It is set in increments of minutes. Then stop and restart the listener for the change to take effect. This will either turn off dead connection detection (DCD) or will increase the time to 30 minutes before the connection can be marked for deletion.