OracleConnectionCache: How to Deal with Stale Connections (Doc ID 358114.1)

Last updated on MARCH 08, 2017

Applies to:

JDBC - Version 10.2.0.3 and later
Information in this document applies to any platform.
***Checked for relevance on 21-Jan-2014***


Goal

You want to detect and cleanup invalid connections in the OracleConnectionCache before they are used by your application.

The OracleConnectionCache can validate the cached connection at the time of assigning it to the application by specifying the ValidateConnection property. Once the connection has been found to be bad, the invalid connections can then be removed from the cache by invoking the refreshCache method.

If ValidateConnection is set to true, invalid connections are tested and their corresponding error is returned to the application like:

"Connection Closed"
or
"Invalid or Stale Connection"

Then the OracleConnectionCacheManager.refreshCache method can be used to close available connections in the cache and recreate them.

Below is an example of how to detect and cleanup invalid connections. The testcase uses keyboard input to pause the program. To experiment with the test case do the following:

1) Run the program.

2) When the program stops at System.in.read(), go to SQL*Plus, identify the SCOTT session using:

 

select username, sid, serial#, machine, program from v$session where username = 'SCOTT'

 

 

and kill it using: 

 

ALTER SYSTEM KILL SESSION 'sid,serial#';



3) Press enter again. The first "dataSource.getConnection" will fail but after refreshing the cache the second getConnection will work.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms