DBCP报错ORA-01000
来源:互联网 发布:网络电视最清楚的软件 编辑:程序博客网 时间:2024/05/29 12:42
使用APACHE DBCP连接oracle数据库一段时间后报错ORA-01000: maximum open cursors exceeded
先查ORACLE的open_cursors参数配置:
select * from v$parameter where name like '%cursor%';
和当前open_cursors统计
SELECT A.SID, MAX(A.VALUE) AS CURRENT_OPEN_CURSORFROM V$SESSTAT A, V$STATNAME BWHERE A.STATISTIC# = B.STATISTIC# AND B.NAME = 'opened cursors current' GROUP BY A.SIDORDER BY MAX(A.VALUE) DESC;
结果:open_cursors:300(max) 、session_cached_cursors:50(max),不同oracle版本的max值有不同。
其中'opened cursors current'只计算type为"open%"的
以下通过JDBC和APACHE DBCP两种方式,使用查询语句进行测试验证,并使用PLSQL监视open_cursor和session
select * from v$open_cursor
一。jdbc方式
1.connection.close() 后,session关闭、cursor都被删除
2. 不关闭connection
(1)关闭statement
cursor删除或者cache,cache时cursor_type: "OPEN" -> "DICTIONARY LOOKUP CURSOR CACHED";
下一个statement打开新的游标,cursor_type: "OPEN"。
(2)不关闭statement
下一个statement打开新的游标,cursor_type: "OPEN",原来的cursor仍保持。
在这种情况下,如果没有关闭connection,可能导致cursor数超限。
二、连接池方式(APACHE DBCP)
基本同jdbc方式,但是需要注意以下几点:
1. connection.close()并未真正关闭,只是返回连接池,所以session也未关闭
2. poolPreparedStatements 设置为true时,statement也不会真正关闭,cursor保持OPEN
所以要注意maximum open cursors exceeded的问题,设置了maxOpenPreparedStatements小于最大允许cursor数时,可避免此问题。
3.若已从连接池获取connection后在DB端kill session,再使用connection会报ORA-00028,并且不会被返回连接池(仍为active状态,不会改成idle状态)。如果捕获异常并调用close,报java.sql.SQLException: already closed.
alter system kill session 'sid,serial';例如:alter system kill session '40,28225';
下次borrowObject获取connection时,如果未配置validation和超时,因为状态是Active,这个connection不会被重新利用。
如果配置了validation,比如validationQuery,那么检查到connection已关闭,那么会重新建立connection并返回。
java.sql.SQLException: ORA-00028: 您的会话己被终止at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1202)at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)...java.sql.SQLException: Connection is closed. (注:这是DBCP异常)at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.checkOpen(PoolingDataSource.java:174)at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:179)at com.sinotrans.demo.SimpleTest.ds(SimpleTest.java:165)at com.sinotrans.demo.SimpleTest.main(SimpleTest.java:33)java.sql.SQLException: Already closed. (注:这是SQL异常)at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:77)at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:180)at com.sinotrans.demo.SimpleTest.ds(SimpleTest.java:173)at com.sinotrans.demo.SimpleTest.main(SimpleTest.java:33)org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhaustedat org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103)at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)at com.sinotrans.demo.SimpleTest.ds(SimpleTest.java:183)at com.sinotrans.demo.SimpleTest.main(SimpleTest.java:33)Caused by: java.util.NoSuchElementException: Timeout waiting for idle objectat org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:756)at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)... 3 more
三、最后的spring配置如下
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"><property name="driverClassName" value="${jdbc.driverClassName}" /><property name="url" value="${jdbc.url}" /><property name="username" value="${jdbc.username}" /><property name="password" value="${jdbc.password}" /><property name="maxActive" value="100" /><property name="maxWait" value="1000" /><property name="poolPreparedStatements" value="false" /><property name="defaultAutoCommit" value="true" /><property name="validationQuery" value="select sysdate from dual" /></bean>
- DBCP报错ORA-01000
- Ora-03113 报错
- 报错ora-32035
- ORA-3136报错
- 数据库报错ORA
- ORA-报错解释
- ORA-09817,ORA-01075报错解决方法
- 转 -- ORA-02068,ORA-03113 报错
- ORA-02068,ORA-03113 报错
- impdp报错ORA-31631、ORA-39122
- ORA-01187,ORA-01110报错
- DBCA报错, ORA-15045 ORA-17502 ORA-15081
- Oracle 报错:ORA-00313、ORA-00312、ORA-27047 解决方法
- Oracle11g报错ORA-39002、ORA-39070、ORA-39087
- ORA-28000报错解决
- 解决ORA-01555报错
- oracle报错ora-01830
- 报错ORA-00600: 参数
- 【性能优化】:likely和unlikely
- fedora17下root用户可以直接登陆
- 大数运算 (C++)
- Winsows下SVN版本控制器的安装与使用
- memset和bzero、metcopy和bcopy等区别
- DBCP报错ORA-01000
- 2013春季SD高校ACM周赛9(SDUT) -A
- 通用面试题:海量数据去重
- [ListView]android ListView之EmptyView
- ftok
- 第一部分 基本语言 第二章 变量和基本类型(2.6typedef名字)(2.7枚举)
- Qt交叉编译与移植
- C#操作XML小结
- 长方柱类