oracle - 没有更多数据可从套接字错误中读取

Question

我们使用 Oracle 作为 Web 应用程序的数据库。该应用程序大部分时间运行良好，但我们收到此“没有更多数据可从套接字读取”错误。

Caused by: java.sql.SQLRecoverableException: No more data to read from socket
    at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1142)
    at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1099)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:288)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:863)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3620)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1869)
    at org.hibernate.loader.Loader.doQuery(Loader.java:718)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
    at org.hibernate.loader.Loader.doList(Loader.java:2449)
    ... 63 more

我们使用 spring、hibernate，我的应用上下文文件中有以下数据源。

<bean class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close" id="dataSource">
        <property name="driverClassName" value="${database.driverClassName}" />
        <property name="url" value="${database.url}" />
        <property name="username" value="${database.username}" />
        <property name="password" value="${database.password}" />
        <property name="defaultAutoCommit" value="false" />
        <property name="initialSize" value="10" />
        <property name="maxActive" value="30" />
        <property name="validationQuery" value="select 1 from dual" />
        <property name="testOnBorrow" value="true" />
        <property name="testOnReturn" value="true" />
        <property name="poolPreparedStatements" value="true" />
        <property name="removeAbandoned" value="true" />
        <property name="logAbandoned" value="true" />
    </bean>

我不确定这是因为应用程序错误、数据库错误还是网络错误。

我们在oracle日志中看到如下内容

Thu Oct 20 10:29:44 2011
Errors in file d:\oracle\diag\rdbms\ads\ads\trace\ads_ora_3836.trc  (incident=31653):
ORA-03137: TTC protocol internal error : [12333] [4] [195] [3] [] [] [] []
Incident details in: d:\oracle\diag\rdbms\ads\ads\incident\incdir_31653\ads_ora_3836_i31653.trc
Thu Oct 20 10:29:45 2011
Trace dumping is performing id=[cdmp_20111020102945]
Thu Oct 20 10:29:49 2011
Sweep [inc][31653]: completed
Sweep [inc2][31653]: completed
Thu Oct 20 10:34:20 2011
Errors in file d:\oracle\diag\rdbms\ads\ads\trace\ads_ora_860.trc  (incident=31645):
ORA-03137: TTC protocol internal error : [12333] [4] [195] [3] [] [] [] []
Incident details in: d:\oracle\diag\rdbms\ads\ads\incident\incdir_31645\ads_ora_860_i31645.trc
Thu Oct 20 10:34:21 2011

Oracle 版本:11.2.0.1.0

Answer 1

对于此类错误，您应该寻求 Oracle 支持。不幸的是你没有提到你使用的是什么 oracle 版本。该错误可能与优化器绑定(bind)窥视有关。根据 oracle 版本的不同，适用不同的解决方法。

你有两种方法来解决这个问题:

• 升级到 11.2
• 设置 oracle 参数 _optim_peek_user_binds = false

当然只有在 oracle 支持人员建议的情况下才应设置下划线参数