clarification of cursors in oracle with jdbc

Question

I have situation where a 3rd party open source product I am using is running out of cursors in Oracle and receiving the error: java.sql.SQLException: ORA-01000: maximum open cursors exceeded

My maximum cursors is set to 1000 and I am trying to figure out if the code that is reaching this limit is doing something incorrectly, or if I simply need to increase my limit.

After some investigation I found a point in the code at which a ResultSet is created, thereby increasing my open cursor count by 1. However, that ResultSet is soon closed after use.... BUT the cursor count remains where it is. I was able to reproduce the logic in a simple JDBC application outside of the 3rd party open source project.

package gov.nyc.doitt.cursor;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CursorTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection("jdbc:oracle:thin:@myhost:1537:mydb", "username", "password");

            // as expected: there are 0 cursors associated with my session at this point

            ps = conn.prepareStatement("select my_column from my_table where my_id = ?");
            ps.setInt(1, 86);

            // as expected: there are 0 cursors associated with my session at this point

            rs = ps.executeQuery(); // opens 1 cursor

            // as expected: there is 1 open cursor associated with my session at this point
        } catch (Throwable t) {
            t.printStackTrace();
        } finally {
            // as expected: there is 1 open cursor associated with my session at this point
            try {
                rs.close();
            } catch (SQLException e) {
                System.err.println("Unable to close rs");
            }
            // not expected: there is still 1 open cursor associated with my session at this point
            try {
                ps.close();
            } catch (SQLException e) {
                System.err.println("Unable to close simplePs");
            }
            // not expected: there is still 1 open cursor associated with my session at this point
            try {
                conn.close();
            } catch (SQLException e) {
                System.err.println("Unable to close conn");
            }
            // as expected: at this point my session is dead and so are all the associated cursors
        }
    }
}

I found some Oracle documentation that made me think that all open cursors would be closed if you closed our ResultSet and PreparedStatements, but my open cursors seem to be hanging around. See this FAQ (http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/basic.htm#1006509) which says "Closing a result set or statement releases the corresponding cursor in the database." Only based on my test that doesn't seem to happen, so I must be lacking some basic understanding.

Can anyone explain how Oracle handles cursors or point me to some documentation that will enlighten me?

Thanks!

Answer 1

Quite long time ago I've encountered a similar problem. As far as I remember, the issue was in delayed garbage collection. Database cursor won't close until garbage collector finds and releases the appropriate object. If statements are created frequently, you can run into this issue. Try to invoke garbage collector manually from time to time:

Runtime r = Runtime.getRuntime();
r.gc();

just to check this supposition.