1 Reply Latest reply on Jan 16, 2004 9:10 PM by Steve Maring

    open cursors and prepared statement cache size

    Steve Maring Newbie

      I'm running JBoss 3.2.2 with Oracle 8.1.7 (+ 8.1.7.4 patch) on Windows with JDK 1.4.2_03

      My init.ora file had "open_cursors = 300" and my app would, after running many queries, eventually crash with "ORA-01000 maximum open cursors exceeded". I am 100% confident that all of my ResultSets and PreparedStatements were being closed properly. I figured my only saving grace was in the connection.close(), but that does not kill the session with Oracle, it only puts the connection back into the JBoss pool.

      I then added:
      <prepared-statement-cache-size>100</prepared-statement-cache-size>

      to my oracle-ds.xml in hopes that JBoss would properly recycle the connection when the prepared statement count got too high. However, what happens now is that on the very first occurance of a PreparedStatement execution I get:
      java.sql.SQLException: Closed Statement

      and I am 100% certain that my code is not prematurely closing the statement.

      Anybody know what is going on here?

      Thanks.
      Steve Maring
      Tarpon Springs, FL

        • 1. Re: open cursors and prepared statement cache size
          Steve Maring Newbie

          Well, I removed the "<prepared-statement-cache-size>" element from my oracle-ds.xml and configured <min-pool-size> to 10, instead of 1. My <idle-timeout-minutes> is 1

          So far it seems to be doing the trick. I think what is happening is that my connections that get put back in the pool are sitting around idle long enough to be recycled and have their Oracle session, along with the cursors, KILLED like the dirty rotten bastards that they are!

          hehehe