Friday, November 21, 2014

Weekend Learning: Maximum open cursors with JDBC 12.1.0.2

Were you aware that "overuse" of the OracleDatabaseMetaData.getTables method in JDBC 12.1.0.2 causes an ORA-01000 error to happen?  "ORA-01000: Maximum Open Cursors Exceeded With OracleDatabaseMetaData.getTables In JDBC 12.1.0.2" in My Oracle Support via document ID 1939417.1 tells the whole (but short) story and which version of the driver has this issue fixed.
Posted by at
Labels: , ,

2 comments:

  1. AnonymousFebruary 19, 2015 at 12:29 AM

    Hi,
    is it possible to provide some detailed information about the 'document ID 1939417.1'? I'm currently facing the same issue and I'm very interested in how and when this issue gets resolved by oracle...

    Thanks & Regards,
    Marco

    ReplyDelete
  2. AnonymousApril 9, 2015 at 7:34 AM

    Getting the same issue during Hibernate schema validation.
    The problem is actually in OracleDatabaseMetaData.getTablesTypes()
    which for opens the cursor for each mapped Table and View
    in a loop for

    select 'TABLE' as table_type from dual
    union select 'VIEW' as table_type from dual
    union select 'SYNONYM' as table_type from dual

    And never closes resultset :(
    10.2.0.2 sources is the most recent I could find suggests that the caller should take care of closing the resultset:
    https://code.google.com/p/ora-jdbc-source/source/browse/trunk/OracleJDBC/src/oracle/jdbc/OracleDatabaseMetaData.java?r=8#1769

    If the schema large enough it finally causes to ORA-0100
    Temporary solution would be increasing OPEN_CURSORS parameter so that it exceeds the number of mapped entities.


    Regards,
    Roman.

    ReplyDelete

Subscribe to: Post Comments (Atom)