检查未关闭的数据库连接proxool.docx

SELECT /*+ rule*/ B.INST_ID, A.EVENT, C.SID, B.SPID, C.MACHINE, C.SECONDS_IN_WAIT, kill -9 || B.SPID, alter system kill session ||C.SID||,||C.SERIAL#||;, E.OBJECT_NAME, dbms_rowid.ROWID_CREATE(1,C.ROW_WAIT_OBJ#,C.ROW_WAIT_FILE#,C.ROW_WAIT_BLOCK#,C.ROW_WAIT_ROW#) RWID, D.SQL_TEXT ? FROM GV$SESSION_WAIT A, ?????? GV$PROCESS????? B, ?????? GV$SESSION????? C, ?????? GV$SQLAREA????? D, ?????? DBA_OBJECTS???? E WHERE A.EVENT = enq: TX - row lock contention ?? AND A.SID = C.SID ?? AND C.PADDR = B.ADDR ?? AND A.INST_ID = C.INST_ID ?? AND A.INST_ID = B.INST_ID ?? AND A.INST_ID = D.INST_ID ?? AND C.SQL_ID = D.SQL_ID(+) ?? AND C.ROW_WAIT_OBJ# = E.OBJECT_ID --? AND A.INST_ID = 2 ORDER BY INST_ID, SECONDS_IN_WAIT DESC; select s.SID,s.MACHINE,s.USERNAME,s.STATUS,s.LOGON_TIME,s2.SQL_TEXT from v$session s,v$sqlarea s2 where s.SQL_ID = s2.SQL_ID order by s.LOGON_TIME desc 以下转帖备份 1.利用oracle数据库的动态性能视图v$open_cursor，即通过游标来查找未释放的数据库连接select * from v$open_cursor where user_name=SCOTT;其中SCOTT为登录的用户名如果java数据库的连接没有关闭通过上面的sql语句就可以看到被查询的sql语句 select * from dept 在被打开的游标里面出现多次。如下所示：123 3997DA58 315 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept 120 39976A20 309 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept 124 3996F9E8 303 SCOTT 39F2EAFC 2979176267 01uk0fqst57ub select * from dept 然后根据sql语句去查找，写java代码是那个sql语句没有关闭。2.通过proxool数据库连接池来查找首先建立一个web project工程testProxool，创建一个cfg包创建LoadDbXml.java类内容如下：package cfg; import java.util.*; import java.io.*; import org.jdom.*; import org.jdom.input.SAXBuilder; import xool.ProxoolException; import xool.ProxoolFacade; public class LoadDbXml { private Element rootElement = null; public static HashMap sqlEle = new HashMap(); //初始化构造方法public LoadDbXml(){ ?? String proxool = D://MyEclipse//workspace//testCharacter//WebRoot//WEB-INF//proxool.xml; ?? getRootElement(proxool); ?? initDBConn(proxool); } //获得根节点public void getRootElement(String m_xmlFilePath) { ?? Document doc = null; ?? try{ ??? File file = null; ??? file = new File(m_xmlFilePath