mercredi 18 février 2015

Oracle data source connection pooling not working used with Spring and JDBCTemplate

Question: Lot of active unclosed physical connections with database even with connection pooling. Can someone tell me why is it so?


I configured the connection pool settings using oracle.jdbc.pool.OracleDataSource. However it seems the physical connections are not getting closed after use. I thought, Since it is connection pooling, the connections will be reused from the pool, so so many physical connections will not be made, but thats not what is happening now!


There are 100+ active physical connections in the database generating from the application [not from plsql developer or any such client tools], due to which it kicks off TNS error while trying to do write operations on database, where as read operations are fine even with large number of active connections.


Here is the Spring configuration,



<bean id="oracleDataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close"
p:URL="${url}"
p:user="${username}"
p:password="${password}"
p:connectionCachingEnabled="true">
<property name="connectionProperties">
<props merge="default">
<prop key="AutoCommit">false</prop>
</props>
</property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
p:dataSource-ref="oracleDataSource" />

<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
p:dataSource-ref="oracleDataSource">
</bean>


The SQL that returned the 100+ active connections is ,



select username, terminal,schemaname, osuser,program from v$session where username = 'grduser'

Aucun commentaire:

Enregistrer un commentaire