Hi all,

Been receiving the above open cursors exceeded error in our application. After initial investigation, we noticed that even afer the statement execution, the cursors are open in the database.

Below is the sample application code,

public void addAudit(String loginID)
{
Session session = ConnectionFactory.getInstance().getSession();
Audit at = new Audit();
try
{
Calendar rightNow = Calendar.getInstance();
Date dt = new Date(System.currentTimeMillis());
at.setAuditID(-1);
at.setUserID(loginID);
at.setAuditType("LOGIN");
at.setUpdateTime(dt);

//Add to sec_audit table
Transaction txn = session.beginTransaction();
session.save(at);
logObj.info("saving into SEC_AUDIT loginid=" + loginID);
txn.commit();

}
catch (HibernateException e)
{
System.err.println("Hibernate Exception" + e.getMessage());
logObj.error(e);
throw new RuntimeException(e);
}
finally
{
try
{
session.close();
}
catch (HibernateException e)
{
System.err.println("Hibernate Exception" + e.getMessage());
logObj.error(e);
throw new RuntimeException(e);
}
}
}

So just wondering whether we are doing something wrong in the code, that prevents closing the cursor.

we tried the option - hibernate.statement_cache.size=0 in the properties file as well.. No luck..

Any help will be much appreciated.

Kind Regards
Shekar.

Which version of Oracle's JDBC drivers are you using?

I'm seeing this right now using their 10g (10.1.0.2.0) drivers. I'm going to test their latest 9i (9.2.0.4) drivers soon and will let you know what I find.

There is oracle parameter open_cursors - increase it

this error can occur if you're taking care of how transaction are finished n you app.

Typically i had this problem with a developper who wasn't using beginTransaction for read only queries.

Hope this can help...

http://www.orafaq.com/error/ora-01000.htm
this must help to find the cause, It takes a few minutes to fix this kind of problems, just use error code for search.

Our database instances are typically set to 500 open cursors, which should be more than adequate for our application (especially in dev and testing environments). We follow standard txn management best practices.

Our particular problem was because of DBCP prepared statement caching, as discussed in the FAQ:

http://hibernate.org/120.html#A9

I'm going to experiment with Oracle JDBC's implicit statement caching:

http://download-west.oracle.com/docs/cd ... m#i1071993

Anyone have experience with this?

Isn't the problem in hibernate code itself ?

I have found this open issue :
http://opensource.atlassian.com/project ... e/HHH-2006