oracle 用jdbc澄清oracle中的游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1903041/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
clarification of cursors in oracle with jdbc
提问by Sarah Haskins
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
我遇到的情况是，我使用的第 3 方开源产品在 Oracle 中的游标用完并收到错误消息：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.
我的最大游标数设置为 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 在使用后很快就关闭了......但游标计数保持不变。我能够在 3rd 方开源项目之外的简单 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!
谢谢！
采纳答案by Egor Rogov
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.
只是为了检查这个假设。
