3 Replies Latest reply on Apr 23, 2002 5:51 PM by Jian Liu

    Open Cursors

    Ralph Lynn Newbie

      I'm hoping someone can help me solve this problem. I seem to be running into a problem with too many Oracle cursors being opened through JDBC and entity beans. In all my code, I close ResultSets, Statements, and Connections in a finally block.

      However, it seems that the database cursors remain open. Can someone explain to me....if I issue the close statements, should I see the database cursors being closed or does that only happen once the connection has actually timed out from being idle?

      Thanks for your help.

        • 1. Re: Open Cursors
          Peter Wörndl Newbie

          Use

          select * from V$open_cursor where user_name=;

          to identify the sql, that causes trouble.

          In my case it was my own code.

          • 2. Re: Open Cursors
            leo yu Newbie

            You should close all statments and resultset as soon as you finish using it. The finally block only closes the last one you used. If you have a while loop accssing a database table, you need to close your statement and resultset within the while loop.

            Leo

            • 3. Re: Open Cursors
              Jian Liu Newbie

              I ran into the same issues (maximum open cursors exceeded) using oracle with open cursors set to 50. I am very positive that all the statements are closed properly. What I noticed, by looking at v_$open_cursor view, is that the open cursors do not drop even there are no hits to the container. When more hits come in, the number of open cursors start to increase and sometimes the number drops, but overall, the number grow.

              Likely the close call on statement does not close the statement. Instead it's put back to the cache and ready for reuse. I assume that these cached statements will be closed before the connection is put back to the dbpool. If my assumption is correct, then I should not see open cursors at all if no hits on container for a while. Correct?