For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Thanks for providing the detailed info - wish more people would do that. That is what made it easy to help you.
According to the Oracle documentation, If I use connection pooling the connection will be returned to the pool as soon as I close the connection, Invalidate it and set it to null See Here
.Correct - the issue is that your code is NOT really using connection pooling.
In my Main application I have a simple loop which makes a connection 200 times by calling RetrieveDBVersion
RetrieveDBVersion
Yes - but it also tries to create 200 connection pools - one for each call to GetOracleConnection.
See the second line?
public static Connection GetOracleConnection(String Enviroment) throws SQLException{ PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
public static Connection GetOracleConnection(String Enviroment) throws SQLException{
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
That 'pds' variable goes out of scope as soon as the method ends. So when you call the method a new datasource is obtained from the factory.
Then you create ONE connection for that new datasource and return it. When the method ends that second 'pds' instance goes out of scope.
Going 'out of scope' does NOT mean the 'pds' instance is garbage collected since there is still a reference to it.
On every method call it does the same thing: get a NEW datasource, create one connection and return it.
Connection conn = JDBCConnection.GetOracleConnection("test");
You try to execute that line 200 times. And each it causes a NEW datasource to be created - specifically it does NOT use the pool previously created because that 'pds' instance was NOT saved in a variable anywhere. Those old 'pds' instances still exist on the stack but you don't have any instance variable to reference them by.
In short your JDBCConnection class is really mixing two DIFFERENT operations: 1) create a connection pool and 2) get a connection from that pool.
The standard way would be to have one method that creates a connection pool and returns that pool (i.e. returns a 'PoolDataSource pds'). Then you would use 'pds.getConnection()' to get a connection from the pool. Then you would 'close' that connection and, under the covers, rather than closing it the pool implementation would return the connection to the pool so it could be used the next time.
When you set the connection invalid the pool implementation would actually remove the connection from the pool - next time you want a connection it will need to actually create one if there are none in the pool.
My loop works fine until I hit the magic number of 68 and then I receive an error which statesjava.sql.SQLException: Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLException: Listener refused the connection with the following error: ORA-12516, TNS:listener could not find available handler with matching protocol stack
My loop works fine until I hit the magic number of 68 and then I receive an error which states
That exception means you have run out of handlers on the SERVER to create new connections.
If I were to create a web app and have a `getConnection()` every time a user logs in, would I not run out of available connections after 68 users?
Yes - using YOUR flawed code or if you tried to create a connection pool that had 68 open connections
No - using a connection pool with a MAX open connections less than 68.
That limit (e.g. '68') will depend on how the server is configured. It could be much higher or much lower depending on the MAX SESSIONS configured on the server.
Thank you so much for that reply. I have been racking my brain for days as to why that was happening. I now understand, I feel silly that I have spend so much time working on this and it did not hit me with that the pool is being created many times over. I guess because all the example I have seen always have a top to bottom approach, my implementation is poor.
Thanks again for clearing me up. Your explanation makes much more sense.
Thank you!
Just for the record and in case someone gets confused as I did. Following @"rp0428" suggestion here is another implementation of the GetOracleConnection
method.
package com.jam.DB;import oracle.ucp.jdbc.PoolDataSource;import oracle.ucp.jdbc.PoolDataSourceFactory;public class Database { private static Database dbIsntance; private static PoolDataSource pds; private Database() { // private constructor // } public static Database getInstance(){ if(dbIsntance==null){ dbIsntance= new Database(); } return dbIsntance; } public PoolDataSource GetPoolSource(){ if(pds==null){ pds = PoolDataSourceFactory.getPoolDataSource(); try { pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource"); pds.setURL("jdbc:oracle:thin:@//localhost:1521/xe"); pds.setUser("system"); pds.setPassword("xxx"); pds.setMaxStatements(15); return pds; } catch(Exception e){ } return pds; } return pds; }}
package com.jam.DB;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;
public class Database {
private static Database dbIsntance;
private static PoolDataSource pds;
private Database() {
// private constructor //
}
public static Database getInstance(){
if(dbIsntance==null){
dbIsntance= new Database();
return dbIsntance;
public PoolDataSource GetPoolSource(){
if(pds==null){
pds = PoolDataSourceFactory.getPoolDataSource();
try {
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/xe");
pds.setUser("system");
pds.setPassword("xxx");
pds.setMaxStatements(15);
return pds;
catch(Exception e){
Than in your code simply call it like this.
Connection conn = Database.getInstance().GetPoolSource().getConnection();
This is technically call a singleton pattern to retrieve the one and only pool instance.
Please refer to UCPSample for an example. Check out other code samples on Github.