Oracle在关闭结果集后不删除游标 [英] Oracle doesn't remove cursors after closing result set

问题描述

注意：我们重复使用单一连接。

  **************** ******************************** 
 public Connection connection（）{
 try {
 if（（connection == null）||（connection.isClosed（）））
 {
 if（connection！= null）
 log.severe ）; 
 connection = DriverManager.getConnection（jdbcURL，username，password）; 
} 
} catch（SQLException e）{
 log.severe（can not connect：+ e.getMessage（））; 
} 
 return connection; 
} 
 ***************************************** ********* 
 
 public IngisObject [] select（String query，String idColumnName，String [] columns）{
 Connection con = connection（）; 
 
向量< IngisObject> objects = new Vector< IngisObject>（）; 
 try {
语句stmt = con.createStatement（）; 
 
 String sql = query; 
 ResultSet rs = stmt.executeQuery（sql）; // oracle增加游标计数
 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在这里不减少游标计数，而是期望的
 stmt.close（）; 
} 
 catch（SQLException ex）{
 System.out.println（query）; 
 ex.printStackTrace（）; 
} 
  

解决方案

init.ora参数 open_cursors 定义会话可以同时拥有的最大打开的游标。它的默认值为50.如果应用程序超过此数字，则会抛出错误ORA-01000：maximum open cursors exceeded。

因此，当不再需要JDBC资源时，必须关闭JDBC资源，特别是java.sql.ResultSet和java.sql.Statement。如果它们未关闭，则应用程序有资源泄露。

如果重新使用Connection对象，您必须知道打开的oracle游标保留打开并在使用中，只要连接存在和事务尚未结束。当应用程序提交时，已打开的游标将被释放。

因此，作为应用程序设计师，您需要知道对最复杂事务所需的打开游标的粗略估计。 / p>

困难在于oracle的内部参数视图（v $ open_cursor，v $ sesstat等）不能显示打开的游标之间的区别，这是可重用的和打开的游标，它们仍然被一个未关闭的ResulSet或Statement阻塞（不可重用！）。

调整init.ora参数的工作原理是这样的（我们的应用程序需要800个游标a最大）

  ALTER SYSTEM SET open_cursors = 800 SCOPE = BOTH; 
  

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();
    }

解决方案

The init.ora parameter open_cursors defines 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.

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.

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 and the transaction has not ended. When the application commits, the opened cursors are released.

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.

Adjusting the init.ora parameter works like this (our application needs 800 cursors at a maximum)

ALTER SYSTEM SET open_cursors = 800 SCOPE=BOTH;

这篇关于Oracle在关闭结果集后不删除游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！