有關正在打開的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.");
       }
      }