检查 数据库锁和程序没有释放数据库连接
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 org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.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);
    SAXBuilder sb = new SAXBuilder();
    doc = sb.build(file);
    rootElement = doc.getRootElement();
   }catch(Exception ex){
    ex.printStackTrace();
   }
}
//初始化数据库参数
public void initDBConn(String m_xmlFilePath)
{
   Element ele = rootElement.getChild("database");
   try{
    Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
    Properties info = new Properties();
    info.setProperty("proxool.maximum-connection-count", ele.getChildText("DBPoolMaxConnection"));
    info.setProperty("proxool.minimum-connection-count", ele.getChildText("DBPoolMinConnection"));
    info.setProperty("proxool.maximum-active-time", ele.getChildText("DBPoolTimeoutValue"));
    info.setProperty("proxool.maximum-connection-lifetime", ele.getChildText("DBPoolTimeoutValue"));
    i