Maximum open cursors exceeded – with hibernate SchemaValidator

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

Answer

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.

Leave a Reply

Your email address will not be published. Required fields are marked *