Exceeding Maximum Idle Time in Java Web Application with Oracle DB

Question

I have a Java web application connecting to an Oracle database running on another machine (not sure if this is relevant or not). I am using DBCP for connection pooling. The web application is running in JBoss 4.2.2 and we are defining our datasource as a bean in Spring.

We are using Hibernate for ORM.

We are getting errors occasionally like so: "ORA-02396: exceeded maximum idle time, please connect again".

I have tried adding properties to our DBCP BasicDataSource called "removeAbandoned" (true) and "removeAbandondedTimeout" (120) to no avail.

Any help would be appreciated. If I need to provide more information, please let me know - I'm not all that knowledgeable about the inner workings of connection pooling, etc...

Answer 1

Try setting the testWhileIdle property to true when configuring your datasource. You'll also need a test query - for Oracle, something like select 1 from dual will suffice.

This will prompt dbcp to nudge any idle connections to keep them fresh.

You can also consider evicting connections that become idle if you don't mind recreating them once they are needed later. Have look at the documentation describing the configuration options for the minEvictableIdleTimeMillis, timeBetweenEvictionRunsMillis and maxIdle / minIdle properties.

Answer 2

See this thread on the spring forums.

The users either ended up switching to c3p0 or adding the testWhileIdle property to the spring configuration:

<prop key="hibernate.dbcp.testWhileIdle">true</prop>

<prop key="hibernate.dbcp.validationQuery">
select * from abc
</prop>

Answer 3

I would switch to the Oracle Universal Connection Pool library. It's optimized for Oracle JDBC Connections, but also will work with any JDBC driver if you ever switch. It's also JDBC 4.0 compliant and is actually being worked on. C3P0, Proxool, DBCP are all kind of stagnant. It will validate you connection by doing a ping to Oracle rather then a query also, which is much faster.

Oracle UCP