使用jdbc澄清oracle中的游标

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

我遇到的情况是我使用的第三方开源产品在Oracle中用完游标并收到错误：java.sql.SQLException：ORA-01000：超出最大打开游标数

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.

我的最大游标设置为1000，我试图弄清楚达到此限制的代码是否正在执行错误操作，或者我是否只需要增加限制。

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.

经过一些调查后，我在代码中找到了一个创建ResultSet的点，从而使我的打开游标数增加1.但是，该ResultSet在使用后很快就会关闭....但是光标数仍保持原样。我能够在第三方开源项目之外的简单JDBC应用程序中重现逻辑。

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.

我发现一些Oracle文档让我觉得如果你关闭了我们的ResultSet和PreparedStatements就会关闭所有打开的游标，但是我的开放游标似乎在闲逛。请参阅此常见问题解答（http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/basic.htm#1006509），其中说明“关闭结果集或语句会在数据库中释放相应的游标”。只是基于我的测试似乎没有发生，所以我必须缺乏一些基本的理解。

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

任何人都可以解释Oracle如何处理游标或指向一些可以启发我的文档吗？

Thanks!

谢谢！

1 个解决方案

#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.

只是为了检查这个假设。