I have broken connections that are closed in JBoss, but not in Oracle?
You have something like a network split where JBoss detects the connection is broken, but can't
actually tell Oracle to close the connection. By default, Oracle does not detect the broken/idle
connection on its side.
User comment about load?
When using connection pooling with Oracle, you may notice that the connections don't close immediately. If your database server is under high load, you may also experience the fatal "out of processes" Oracle error (Oracle has one process per connection, and each database instance can have a fixed maximum number of processes), even though the majority of connections are currently closed (or marked as "Inactive").
The fix (Oracle detects the broken connection)
There is a configuration in listeners.ora where you can set a connection timeout.
This will close connections that have not been used by the client for that length of time. In Oracle 8i and earlier, this setting is called CONNECT_TIMEOUT. In Oracle 9i and later, this setting is called INBOUND_CONNECT_TIMEOUT (see the Oracle docs for more information).
NOTE: You'll probably want to set the JBoss's <idle-timeout-seconds/> in oracle-ds.xml to be consistent with the Oracle setting. The default value is 15 minutes idle. But this is not the exact
time when after which the connection will be closed. It checks every half-interval, e.g. with the
default config you could get this "pathological" case:
h:mm:ss 0:00:00 - JBoss idle connection remover runs 0:00:01 - Last use of connection by JBoss 0:07:30 - JBoss idle connection remover runs again 0:15:00 - JBoss idle connection remover runs again 0:15:01 - The connection is now considered idle by JBoss 0:22:30 - JBoss idle connection remover runs again and this time closes the connection
Note: I don't think INBOUND_CONNECT_TIMEOUT would do anything to prevent this from happening, according to Oracle documentation, it controls initial connection time out, see Oracle documentation Chapter 10