Oracle关闭的连接、无法从套接字获取更多的数据

14:48:15.272 INFO  o.s.j.s.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
14:48:15.281 INFO  c.a.s.impl.DataAccessServiceImpl - UPDATE MEASUREPOINT TIME EXCEPTION=>370826B0012000411410,{}
org.springframework.dao.RecoverableDataAccessException:
### Error querying database.  Cause: java.sql.SQLRecoverableException: 无法从套接字读取更多的数据
### The error may exist in class path resource [mapper/StressDataInfoMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT ACQUISITION_TIME   FROM STRESS_DATAINFO WHERE MP_ID=?   ORDER BY   ACQUISITION_TIME DESC
### Cause: java.sql.SQLRecoverableException: 无法从套接字读取更多的数据
; SQL []; 无法从套接字读取更多的数据; nested exception is java.sql.SQLRecoverableException: 无法从套接字读取更多的数据
        at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:98)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
        at com.sun.proxy.$Proxy80.selectList(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
        at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
        at com.sun.proxy.$Proxy86.getPointTimeByID(Unknown Source)
        at com.akxy.service.impl.DataAccessServiceImpl.updatePointTime(DataAccessServiceImpl.java:369)
        at com.akxy.controller.DataAccessController$1.run(DataAccessController.java:90)
        at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
        at java.util.concurrent.FutureTask.run(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLRecoverableException: 无法从套接字读取更多的数据
        at oracle.jdbc.driver.T4CMAREngineStream.unmarshalUB1(T4CMAREngineStream.java:453)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:390)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3923)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)
        at sun.reflect.GeneratedMethodAccessor80.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
        at com.sun.proxy.$Proxy104.execute(Unknown Source)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
        at sun.reflect.GeneratedMethodAccessor77.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
        ... 13 common frames omitted

 

2019-11-14 14:54:56.764  INFO 12792 --- [       Thread-5] o.s.s.quartz.SchedulerFactoryBean        : Shutting down Quartz Scheduler
14:54:57.731 INFO  c.a.s.impl.DataAccessServiceImpl - UPDATE MEASUREPOINT TIME EXCEPTION=>370811B0012000311501,{}
org.springframework.dao.RecoverableDataAccessException:
### Error querying database.  Cause: java.sql.SQLRecoverableException: 关闭的连接
### The error may exist in class path resource [mapper/StressDataInfoMapper.xml]
### The error may involve com.akxy.mapper.StressDataInfoMapper.getPointTimeByID
### The error occurred while executing a query
### Cause: java.sql.SQLRecoverableException: 关闭的连接
; SQL []; 关闭的连接; nested exception is java.sql.SQLRecoverableException: 关闭的连接
        at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:98)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
        at com.sun.proxy.$Proxy79.selectList(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
        at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
        at com.sun.proxy.$Proxy87.getPointTimeByID(Unknown Source)
        at com.akxy.service.impl.DataAccessServiceImpl.updatePointTime(DataAccessServiceImpl.java:369)
        at com.akxy.controller.DataAccessController$1.run(DataAccessController.java:90)
        at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
        at java.util.concurrent.FutureTask.run(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLRecoverableException: 关闭的连接
        at oracle.jdbc.driver.PhysicalConnection.getAutoCommit(PhysicalConnection.java:4337)
        at sun.reflect.GeneratedMethodAccessor114.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
        at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:79)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
        at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
        at com.sun.proxy.$Proxy67.getAutoCommit(Unknown Source)
        at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:83)
        at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
        at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:336)
        at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:84)
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
        at sun.reflect.GeneratedMethodAccessor113.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
        ... 13 common frames omitted

 

在数据库级别执行以下SQL，设置隐含参数，即可避免出现此问题。

alter system set "_optimizer_connect_by_cost_based" = false scope=both ;