Java 关闭结果集后，Oracle 不会删除游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2560350/
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
Oracle doesn't remove cursors after closing result set
提问by Vladimir
Note: we reuse single connection.
注意：我们重用单个连接。
************************************************
public Connection connection() {
try {
if ((connection == null) || (connection.isClosed()))
{
if (connection!=null)
log.severe("Connection was closed !");
connection = DriverManager.getConnection(jdbcURL, username, password);
}
} catch (SQLException e) {
log.severe("can't connect: " + e.getMessage());
}
return connection;
}
**************************************************
public IngisObject[] select(String query, String idColumnName, String[] columns) {
Connection con = connection();
Vector<IngisObject> objects = new Vector<IngisObject>();
try {
Statement stmt = con.createStatement();
String sql = query;
ResultSet rs =stmt.executeQuery(sql);//oracle increases cursors count here
while(rs.next()) {
IngisObject o = new IngisObject("New Result");
o.setIdColumnName(idColumnName);
o.setDatabase(this);
for(String column: columns)
o.attrs().put(column, rs.getObject(column));
objects.add(o);
}
rs.close();// oracle don't decrease cursor count here, while it's expected
stmt.close();
}
catch (SQLException ex) {
System.out.println(query);
ex.printStackTrace();
}
采纳答案by Oliver Michels
The init.ora parameter open_cursorsdefines the maximum of opened cursors a session can have at once. It has a default value of 50. If the application exceeds this number the error "ORA-01000: maximum open cursors exceeded" is raised.
init.ora 参数open_cursors定义了一个会话可以同时拥有的最大打开游标数。它的默认值是 50。如果应用程序超过此数字，则会引发错误“ORA-01000：超出最大打开游标数”。
Therefore it's mandatory to close the JDBC resources when they are not needed any longer, in particular java.sql.ResultSet and java.sql.Statement. If they are not closed, the application has a resource leak.
因此，当不再需要 JDBC 资源时必须关闭它们，特别是 java.sql.ResultSet 和 java.sql.Statement。如果它们没有关闭，则应用程序存在资源泄漏。
In case of reusing the Connection object, you must be aware of the fact that the opened oracle cursors are kept open and in use as long the connection exists andthe transaction has not ended. When the application commits, the opened cursors are released.
在重用 Connection 对象的情况下，您必须意识到只要连接存在且事务尚未结束，打开的 oracle 游标就会保持打开和使用状态。当应用程序提交时，打开的游标被释放。
Therefore as an application designer you need to know a rough estimation of the needed open cursors for your most complex transaction.
因此，作为应用程序设计人员，您需要粗略估计最复杂的事务所需的打开游标。
The difficulty lies in the inability of oracle's internal parameter views (v$open_cursor, v$sesstat, et. al.) to show the difference between opened cursors, which are reusable and opened cursors, which are still blocked (not reusable!) by an unclosed ResulSet or Statement. If you close all Statement and ResultSet objects in your finally block, your application is perfectly fine.
难点在于 oracle 的内部参数视图（v$open_cursor、v$sesstat 等）无法显示可重用的已打开游标和仍被（不可重用！）一个未封闭的 ResultSet 或 Statement。如果您关闭 finally 块中的所有 Statement 和 ResultSet 对象，您的应用程序就完全没有问题。
Adjusting the init.ora parameter works like this (our application needs 800 cursors at a maximum)
调整init.ora参数是这样的（我们的应用最多需要800个游标）
ALTER SYSTEM SET open_cursors = 800 SCOPE=BOTH;
回答by Doug Porter
Normally you would put the close statements for your ResultSet and Statement into a finallyblock to ensure that they are called even if an exception occurs (could be the issue you are having here). In your current code, if a SQLException occurs then the two close( ) method calls will never occur and cursors would be left open.
通常，您会将 ResultSet 和 Statement 的 close 语句放入一个finally块中，以确保即使发生异常也能调用它们（可能是您在这里遇到的问题）。在您当前的代码中，如果发生 SQLException，那么两个 close() 方法调用将永远不会发生并且游标将保持打开状态。
Also what query are you using in Oracle to see the count of open cursors?
另外，您在 Oracle 中使用什么查询来查看打开游标的数量？
Edit:
That code should be closing the cursor. If it isn't then you should be able to see a 1 to 1 correlation of calling your method and the cursor count going up by 1. Be sure there isn't some unexpected process that is causing the cursor count to go up.
编辑：
该代码应该关闭光标。如果不是，那么您应该能够看到调用您的方法和游标计数增加 1 之间的 1 比 1 相关性。确保没有一些意外过程导致游标计数增加。
If you have the privileges, you can run this query against the database to see the open cursor count by sid to see if maybe it is some other process that is increasing the cursors and not yours specifically. It will pull back any with more than 10 cursors open, you can raise this to filter out the noise or narrow it specifically by username or osuser:
如果您有权限，您可以对数据库运行此查询以查看 sid 的打开游标计数，以查看是否是其他某个进程正在增加游标而不是您的特定进程。它将拉回打开超过 10 个光标的任何光标，您可以提高它以过滤掉噪音或专门通过用户名或 osuser 缩小噪音：
select oc.sid,
count(*) numCur,
s.username username,
s.osuser osuser,
oc.sql_text,
s.program
from v$open_cursor oc,
v$session s
where s.sid = oc.sid
group by oc.sid,
oc.sql_text,
s.username,
s.osuser,
s.program
having count(*) > 10
order by oc.sid;
Another query that may be helpful, in case multiple sid's are using the same query string so the above does not reveal the offender well:
另一个可能有用的查询，以防多个 sid 使用相同的查询字符串，因此上述内容不能很好地揭示违规者：
select oc.sql_text, count(*)
from v$open_cursor oc
group by oc.sql_text
having count(*) > 10
order by count(*) desc;
回答by duffymo
The correct way to do it is to close every resource in a finally block in its own try/catch block. I usually use a static utility class like this:
正确的做法是在自己的 try/catch 块中关闭 finally 块中的每个资源。我通常使用这样的静态实用程序类：
public class DatabaseUtils
{
public static void close(Connection connection)
{
try
{
if (connection != null)
{
connection.close();
}
}
catch (SQLException e)
{
// log exception here.
}
}
// similar methods for ResultSet and Statement
}
So I'd write your code like this:
所以我会这样写你的代码：
public IngisObject[] select(String query, String idColumnName, String[] columns) {
Vector<IngisObject> objects = new Vector<IngisObject>();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try
{
connection = connection();
stmt = con.createStatement();
// This is a SQL injection attack waiting to happen; I'd recommend PreparedStatemen
String sql = query;
rs =stmt.executeQuery(sql);//oracle increases cursors count here
while(rs.next())
{
IngisObject o = new IngisObject("New Result");
o.setIdColumnName(idColumnName);
o.setDatabase(this);
for(String column: columns) o.attrs().put(column, rs.getObject(column));
objects.add(o);
}
}
catch (SQLException ex)
{
System.out.println(query);
ex.printStackTrace();
}
finally
{
DatabaseUtils.close(rs);
DatabaseUtils.close(stmt);
DatabaseUtils.close(con);
}
回答by FrVaBe
I just had the same problem and found that - if you do not close the connection(because you will maybe reuse it later on) - you at least have to do a connection.rollback()or connection.commit()to free the open cursors togehther with closing the ResultSet and Statements.
我刚刚遇到了同样的问题，发现 - 如果你不关闭连接（因为你以后可能会重用它） - 你至少必须做一个connection.rollback()或connection.commit()来释放打开游标与关闭 ResultSet 和 Statements 一起。
