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.");
}
}
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1678992