Maximum open cursors exceeded - with hibernate SchemaValidator

Question

Our Oracle 11g database contains 298 tables(10 recently added), (+100 sequences), we declared 500 cursors.

When starting our WebApplication (Tomcat 7.0, jdbc pool), at sessionFactory initialization when hibernate validates schema it uses all cursors (cf below).

Is there anything known in order for hibernate to be less greedy with Oracle cursors ?

Please note that this problem has nothing to do with the handling of prepared statements or hibernate entities as I do not work with any of them at this step.

    Caused by: org.hibernate.exception.GenericJDBCException: could not get table metadata: MYTABLE
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
        at org.hibernate.tool.hbm2ddl.DatabaseMetadata.getTableMetadata(DatabaseMetadata.java:105)
        at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1080)
        at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:116)
        at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:317)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1300)
        at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:859)
        at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:863)
        at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:782)
        at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:188)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1573)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1511)
        ... 34 more
    Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
        at oracle.jdbc.driver.OracleDatabaseMetaData.getColumnsWithWildcards(OracleDatabaseMetaData.java:350)
        at oracle.jdbc.driver.OracleDatabaseMetaData.getColumns(OracleDatabaseMetaData.java:128)
        at org.hibernate.tool.hbm2ddl.TableMetadata.initColumns(TableMetadata.java:146)
        at org.hibernate.tool.hbm2ddl.TableMetadata.<init>(TableMetadata.java:32)
        at org.hibernate.tool.hbm2ddl.DatabaseMetadata.getTableMetadata(DatabaseMetadata.java:90)
        ... 44 more

Possible duplicate of [java.sql.SQLException: - ORA-01000: maximum open cursors exceeded](http://stackoverflow.com/questions/12192592/java-sql-sqlexception-ora-01000-maximum-open-cursors-exceeded) — Boneist, Feb 05 '16 at 17:46
IMO the suggested dupe is not a duplicate in this case, as the central question here is that OP wants to know how to prevent Hibernate from using all the cursors and erroring out. Note, however, that [this answer](http://stackoverflow.com/a/24461134/213136) to the proposed dupe may be of interest to OP. Also note that Hibernate apparently has (or perhaps had) a [bug pertaining to using CollectionRead holding open cursors](https://hibernate.atlassian.net/browse/HHH-8811) (apparently fixed starting in versions 4.3.1 and 6.0.0). Perhaps an upgrade is in order? Best of luck. — Bob Jarvis - Слава Україні, Feb 05 '16 at 17:54
Please note that I'm not doing any request, just hibernate doing its validation at starting — yunandtidus, Feb 05 '16 at 18:31
@BobJarvis I'm using hibernate 3, and the bug you linked appears in v4.3.0. — yunandtidus, Feb 05 '16 at 18:33

score 6 · Accepted Answer · answered Feb 05 '16 at 18:55

6

If your driver version is 12.1.0.2 then this is a known bug:

https://community.oracle.com/thread/3682300

In a nutshell: DatabaseMetaData.getTableTypes() creates a Statement but never closes it, it leaves the cursor open.

Most probably Hibernate calls getTableTypes() quite often during schema validation and thus you are affected by this bug.

The driver version 12.1.0.1 is not affected by this, so you might want to downgrade.

This is logged under Bug #19632480 so if you have access to MOS you can download a patch for this.

answered Feb 05 '16 at 18:55

Would never thought about digging on this side. Thanks a lot for pointing it out. – yunandtidus Feb 05 '16 at 19:01
Here are more information about the issue: https://github.com/javaee/jpa-spec/issues/162 . – Antonio Petricca Jun 07 '18 at 09:15
Here is some information from an IBM site: https://www.ibm.com/support/pages/when-using-ibm-content-navigator-you-may-experience-ora-01000-maximum-open-cursors-exceeded-depending-oracle-jdbc-driver-version-1210x-used – miracle173 Jun 30 '20 at 12:59

Maximum open cursors exceeded - with hibernate SchemaValidator

1 Answers1