"java.sql.SQLException: Cannot create connections. There are no available connections: oracle.ucp.NoAvailableConnectionsException" Occurs When ConnectionWaitTimeout is Set to Zero (Doc ID 2798991.1)

Last updated on DECEMBER 05, 2024

Applies to:

Symptoms

After upgrading JDBC/UCP drivers, the below exception is seen very often but not all the time:

2021-04-27 18:24:39,101 (CEST) CTX= ERROR <PKG.CLASS_NAME> An SQLException occurs for pool <POOL_NAME>:
java.sql.SQLException: Cannot create connections. There are no available connections: oracle.ucp.NoAvailableConnectionsException
   at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:489) ~[ucp-21.1.0.0.jar:21.1.0.0.0]
   at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:166) ~[ucp-21.1.0.0.jar:21.1.0.0.0]
   at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1857) ~[ucp-21.1.0.0.jar:21.1.0.0.0]
   at oracle.ucp.jdbc.PoolDataSourceImpl.access$300(PoolDataSourceImpl.java:220) ~[ucp-21.1.0.0.jar:21.1.0.0.0]
   at oracle.ucp.jdbc.PoolDataSourceImpl$3.build(PoolDataSourceImpl.java:3976) ~[ucp-21.1.0.0.jar:21.1.0.0.0]
   at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1784) ~[ucp-21.1.0.0.jar:21.1.0.0.0]
   at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1739) ~[ucp-21.1.0.0.jar:21.1.0.0.0]
   at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1725) ~[ucp-21.1.0.0.jar:21.1.0.0.0]
   :::::::::::::
Caused by: oracle.ucp.NoAvailableConnectionsException
   at oracle.ucp.jdbc.oracle.OracleConnectionConnectionPool.borrowConnection(OracleConnectionConnectionPool.java:177) ~[ucp-21.1.0.0.jar:21.1.0.0.0]
   at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1839) ~[ucp-21.1.0.0.jar:21.1.0.0.0]
   ... 17 more

The connection pool is big (100 connections) and the exception occurs since the first call to get a connection from the pool.  The pool may still have lots of free connections at that time.

Note : UCP pool has been configured to disable connectionWaitTimeout (same configuration as with version 12.2.0.1.0):

It was observed that when connectionWaitTimeout is set to default or 5, the error oracle.ucp.NoAvailableConnectionsException does not occur.
The objective is to not activate connectionWaitTimeout in production. So it has been disabled as suggested in:

https://docs.oracle.com/en/database/oracle/oracle-database/21/jjucp/stale-ucp-connections.html#GUID-832A537F-B76C-4A02-BDDE-CD30A2AF6656

Why does this exception occur when connectionWaitTimeout is disabled by setting it to Zero (0) ?

Changes

JDBC/UCP drivers were upgraded from 12.2.0.1.0 to 21.1.0.0.0.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.