检查数据库锁和程序没有释放数据库连接

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"));
    info.setProperty("proxool.prototype-count", ele.getChildText("DBPoolPrototypeCount"));
    info.setProperty("proxool.trace", "true");
    info.setProperty("user", ele.getChildText("DBUser"));
    info.setProperty("password", ele.getChildText("DBPassword"));
             
    String url = "proxool." + ele.getChildText("DBAlias") + ":" + ele.getChildText("DBDrivername") + ":" + ele.getChildText("DBDriverurl");
    ProxoolFacade.registerConnectionPool(url, info);
   }catch (ProxoolException e) {
    e.printStackTrace();
   }catch (ClassNotFoundException e) {
    e.printStackTrace();
   }
}
}

创建一个servlet类，用于启动web工程时初始化数据库InitDbXml

package cfg;

import javax.servlet.http.HttpServlet;
import javax.servlet.ServletConfig;

public class InitDbXml extends HttpServlet{

/**
* 初始化servlet，调用初始化数据库类
*/
private static final long serialVersionUID = 1L;

public void init(ServletConfig config){
   new LoadDbXml();
}
}
在WEB-INF下创建一个proxool.xml文件内容如下：

<?xml version="1.0" encoding="utf-8"?>
<dbconfig>
<database>
   <DBSMType>ORACLE</DBSMType>
   <DBAlias>eomsdb</DBAlias>
   <DBDrivername>oracle.jdbc.driver.OracleDriver</DBDrivername>
   <DBDriverurl>jdbc:oracle:thin:@192.168.20.92:1521:chshsid</DBDriverurl>
   <DBUser>scott</DBUser>
   <DBPassword>tiger</DBPassword>
   <DBPoolName>orpool</DBPoolName>
   <DBPoolMinConnection>1</DBPoolMinConnection>
   <DBPoolPrototypeCount>1</DBPoolPrototypeCount>
   <DBPoolMaxConnection>3</DBPoolMaxConnection>
   <DBPoolTimeoutValue>20000</DBPoolTimeoutValue>
</database>
</dbconfig>

在web.xml文件里面加入以下内容：

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<!--将采用的字符编码配置成应用初始化参数而不是过滤器私有的初始化参数是因为在JSP和其他地方也可能需要使用-->

<!-- 连接池的监控环境开始，项目部署时要删除 -->
   <servlet>
     <servlet-name>proxooladmin</servlet-name>
     <servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>
   </servlet>
   <servlet-mapping>
     <servlet-name>proxooladmin</servlet-name>
     <url-pattern>/dbadmin</url-pattern>
   </servlet-mapping>
<!-- 连接池的监控环境结束-->
<!-- 初始化数据库连接start -->
<servlet>
   <servlet-name>initDbXml</servlet-name>
   <servlet-class>cfg.InitDbXml</servlet-class>
   <load-on-startup>1</load-on-startup>
</servlet>

<!-- 初始化数据库连接end -->
</web-app>
在/WebRoot/admin下新建两个jsp文件closeConn.jsp内容如下：

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="cfg.LoadDbXml"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">
    <title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">   
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>&nbsp;
<%
      PreparedStatement ps = null;
      Connection conn = null;
      ResultSet rs = null;
   try {
    conn = DriverManager.getConnection("proxool.eomsdb");
    ps = conn.prepareStatement(" select * from emp ");
    rs = ps.executeQuery();
    out.println("<table bgcolor='green' border='1' width='400' height='500'>");
    while(rs.next()){
     out.println("<tr><td>" + rs.getString(1)+"</td><td>" + rs.getString(2) + "</td></tr>");
    }
    out.println("</table>");
   } catch (SQLException e){
    e.printStackTrace();
   }finally{
    if(rs!=null)
     rs.close();
    if(ps!=null)
     ps.close();
    if(conn!=null)
     conn.close();
   }
   %>
</body>
</html>
创建没有关闭数据库连接的jsp文件noCloseConn.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="cfg.LoadDbXml"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">
    <title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">   
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>&nbsp;
<%
      PreparedStatement ps = null;
      Connection conn = null;
      ResultSet rs = null;
   try {
    conn = DriverManager.getConnection("proxool.eomsdb");
    ps = conn.prepareStatement(" select * from dept ");
    rs = ps.executeQuery();
    out.println("<table bgcolor='green' border='1' width='400' height='500'>");
    while(rs.next()){
     out.println("<tr><td>" + rs.getString(1)+"</td><td>" + rs.getString(2) + "</td></tr>");
    }
    out.println("</table>");
   } catch (SQLException e) {
    e.printStackTrace();
   }
   //数据库没有进行连接
   %>
</body>
</html>


我在proxool.xml文件设置了最大连接数为3

如果http://localhost:8080/testProxool/admin/noCloseConn.jsp该页面连续被刷新四次，后台就会报出错误，
java.sql.SQLException: Couldn't get connection because we are at maximum connection count (3/3) and there are none available
at org.logicalcobwebs.proxool.Prototyper.quickRefuse(Prototyper.java:309)
at org.logicalcobwebs.proxool.ConnectionPool.getConnection(ConnectionPool.java:152)
at org.logicalcobwebs.proxool.ProxoolDriver.connect(ProxoolDriver.java:89)
at java.sql.DriverManager.getConnection(DriverManager.java:525)

我们访问http://localhost:8080/testProxool/dbadmin连接就会发现在

Proxool 0.9.1 (23-Aug-2008 11:10)
Definition
Snapshot
alias: eomsdb
Start date: 2010-06-15 18:55:01
Snapshot: 19:14:59
Connections: 3 (active), 0 (available), 3 (max)


Served: 38
Refused: 73
Details:
(click ID to drill down) # born last
start lap
(ms) thread
41 19:14:56 19:14:56 3093   http-8080-Processor25
40 19:14:56 19:14:56 3312   http-8080-Processor23
39 19:14:53 19:14:56 3859   http-8080-Processor23

Connection #41 sql = select * from dept ;proxy = bdb6aedelegate = 12478a9url = jdbc:oracle:thin:@192.168.20.92:1521:chshsid



访问select * from dept ; sql语句没有断开数据库连接，可以根据sql语句找到相应的java类。

问题便可以轻易的解决，可以不用自己手工写数据库连接池，来用proxool连接池，这个被公认的比较好的数据库连接池。