C3P0 connection pool reported ORA-01000: maximum open cursors exceeded error

Hibernate ORM
1

I am working with the Hibernate 3.6 and c3p0-0.9.2-pre1. I am getting Exception ORA-01000: maximum open cursors exceeded error while using the C3p0 configurations in the hibernate.cfg.xml.

I have a Oracle’s DatabaseSchema.conf file in which I have around 200 create table statement, while validating that Oracle’s Database Schema, I encountered a weird scenario:

  1. If I did not use connection pooling, then no problem, once closed Connection, database released the opened cursors, and open_cursors count did not increased significantly, it will remain under default open_cursors count i.e 300.

  2. But if we used connection pooling, then open_cursors count increased significantly and crosses the default open_cursors count i.e 300 and reported exception ORA-01000: maximum open cursors exceeded error.

I have already tried the following approaches :

  1. Set the value to ‘0’ of the below properties in hibernate.cfg.xml, In order to disable the statement cache of the C3p0.
    property name=“hibernate.c3p0.max_statements”
    property name=“hibernate.c3p0.maxStatementsPerConnection”

  2. After closing SESSION , CONNECTION , RESULTSET assign them NULL.

SESSION:
if(session != null && session.isOpen()) {
session.close();
session = null;
}

RESULTSET :
if(rs != null) {
rs.close();
rs=null;
}

CONNECTION :
if(conn != null) {
conn.close();
conn=null;
}

  1. Used Various Permutation and Combination in configuration of C3P0 defined in the hibernate.cfg.xml

Ex: Subsequently Increased the numHelperThreads and observed the OPEN_CURSOR count, changed the pool’s min and max size. In similar way I have changed the other configurations also.

  1. Used the Connection Provider class in the C3P0 configuration and imported the hibernate-c3p0 jar compatible with the Hibernate 3.6.

  2. Upgraded the Hibernate version from 3.6 to 5.2.

  3. Used the DBCP Connection Pooling Mechanism instead of the C3P0.

Approaches from 1-6 results in the High OPEN_CURSOR counts and Only Approach 7 results in the desired outcome.

What else should I tried to work with the C3P0?

2

This sounds like either you are not closing Statements properly. If you are using scroll() or stream() make sure you are also closing the cursors.