有关正在打开的sql语句（open_cursor之诊断）

在java程序当中，如果statement或resultset处理不当，就很可能会出现ORA-01000 maximum open cursors exceeded的异常，并且迅速耗尽数据库cursor资源，下面通过一个小的示例来逐步诊断问题并解决此类问题。

1： 统计 当前sid打开的游标数量，其中a.value表示打开的数量，a.sid表示当前用户的sid，语句如下：

select a.value, a.sid from v$mystat a, v$statname b where a.statistic# = b.statistic# and b.name = 'opened cursors current'

_sid = a.sid;

2：根据1中查找出的sid，查找出正在执行的sql语句

select sql_text from V$OPEN_CURSOR where sid =_sid

3：根据sql_text可以在程序中找到执行的地方，并且将resultset和statement关闭。

下面是个简单的程序：

try {
      String query = "select a.value, b.name, a.sid from v$mystat a, v$statname b where a.statistic# = b.statistic# and b.name = 'opened cursors current'";
      _stmt = connection.createStatement();
     _ rs = stmt.executeQuery(query);
      
      if (_rs.next()) {
          temprs = _rs;
       if (_rs.getLong(1) > 1) {   //this shows rs must be large than 1
           //----for test start.
           int sid = temprs.getInt(3);
        String sid_sql = "select sql_text from V$OPEN_CURSOR where sid =" + sid;
           Statement sid_stmt = null;
        ResultSet sid_rs = null;
        
        sid_stmt = connection.createStatement();
        sid_rs = sid_stmt.executeQuery(sid_sql);
        
        while(sid_rs.next()){
         System.out.println("The sql_text of sid " + sid + " is: " + sid_rs.getString(1));
        }
        
        sid_rs.close();
        sid_stmt.close();
        //----for test end.
        System.out.println("The number of the result set for this conn: " + temprs.getLong(1) + "  SID : " + sid);
        throw new Exception("this connection has a opened cursor.");
       }
      }