DBCP报错ORA-01000

使用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_CURSOR
FROM V$SESSTAT A, V$STATNAME B
WHERE A.STATISTIC# = B.STATISTIC#  
AND B.NAME = 'opened cursors current'  
GROUP BY A.SID
ORDER 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 exhausted
	at 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 object
	at 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>