一个奇怪的SQL误错，请高手帮忙看一下。ORA-03113:通信通道的文件结束

Question

Select *
  From (Select A.*, ROWNUM RN
          From (Select caseinfotable.ID,
                       caseinfotable.CASEID,
                       caseinfotable.INSURNO,
                       XZCLASS,
                       Insuredpeople,
                       ISREN,
                       ISWU,
                       Compensation,
                       Replacementsum,
                       MaintenanceTime,
                       PEOPLESUM,
                       ITEMSSUM,
                       RescueSUM,
                       otherSUM,
                       CASESUM,
                       TJDATE,
                       ISFZ,
                       ISJC,
                       basorganization.chiname as Commitmentdept,
                       COMPENSATIONCASENUMBER,
                       othernumber,
                       (Operators || basemployee.employeename) as operators,
                       operdept,
                       reportdate,
                       outcasedate,
                       (case
                         when casetype = 0 then
                          '待查勘'
                         when casetype = 1 then
                          '查勘完成'
                         when casetype = 2 then
                          '复勘完成'
                         when casetype = 20 then
                          '申请复勘'
                         when casetype = 3 then
                          '定损完成'
                         when casetype = 30 then
                          '打回定损'
                         when casetype = 4 then
                          '核损完成'
                         when casetype = 40 then
                          '核损上报'
                         when casetype = 5 then
                          '核价完成'
                         when casetype = 50 then
                          '核价上报'
                         when casetype = 6 then
                          '稽查完成'
                         when casetype = 7 then
                          '理算完成'
                         when casetype = 8 then
                          '核赔完成'
                         when casetype = 80 then
                          '核赔上报'
                         when casetype = 9 then
                          '核批完成'
                         when casetype = 10 then
                          '拒赔完成'
                         when casetype = 11 then
                          '销案完成'
                         when casetype = 12 then
                          '结案完成'
                         when casetype = 13 then
                          '撤案完成'
                         when casetype = 14 then
                          '赔案批改'
                         when casetype = 15 then
                          '不立案'
                       end) casetype,
                       (case
                         when peoplecasetype = 0 then
                          '待查勘'
                         when peoplecasetype = 1 then
                          '查勘完成'
                         when peoplecasetype = 2 then
                          '复勘完成'
                         when peoplecasetype = 20 then
                          '申请复勘'
                         when peoplecasetype = 31 then
                          '定损完成'
                         when peoplecasetype = 310 then
                          '打回定损'
                         when peoplecasetype = 41 then
                          '核价完成'
                         when peoplecasetype = 410 then
                          '核价上报'
                       end) peoplecasetype,
                       (case
                         when wucasetype = 0 then
                          '待查勘'
                         when wucasetype = 1 then
                          '查勘完成'
                         when wucasetype = 2 then
                          '复勘完成'
                         when wucasetype = 20 then
                          '申请复勘'
                         when wucasetype = 32 then
                          '定损完成'
                         when wucasetype = 320 then
                          '打回定损'
                         when wucasetype = 42 then
                          '核价完成'
                         when wucasetype = 420 then
                          '核价上报'
                       end) wucasetype,
                       casetimetable.A1,
                       casetimetable.A3,
                       casetimetable.A4,
                       casetimetable.A5,
                       casetimetable.A7,
                       casetimetable.A8
                  From CASEINFOTABLE,
                       basorganization,
                       basemployee,
                       casetimetable
                 where caseinfotable.commitmentdept =
                       basorganization.organizationcode
                   and caseinfotable.operators = basemployee.engname
                   And caseinfotable.caseid = casetimetable.caseid
                   And CaseType >= 1
                   and CaseType < 3
                   and caseinfotable.iscar = 1
                   And (COMMITMENTDEPT Like 'AEAAAC' or
                       OPERDEPT Like 'AEAAAC%' or SANGBAODEPT Like 'AEAAAC%' or
                       soulidept Like 'AEAAAC%' OR
                       COMMITMENTDEPT Like 'AEAAAF' or
                       OPERDEPT Like 'AEAAAF%' or SANGBAODEPT Like 'AEAAAF%' or
                       soulidept Like 'AEAAAF%')
                 Order by CaseinfoTable.TjDate Desc) A
         Where ROWNUM <= 25)
 Where RN >= 1执行这个SQL，会报两个错。提示：ORA-03113:通信通道的文件结束。ORA-03114:未连接到ORACLE。
同样是这个SQL，以前都不会报错，就是最近才报错，不能运行了，请高手指点一二。如果把上面这个SQL，加上这一个条件，就可以正常执行了。and caseinfotable.tjdate >= date'2004-1-1'

Answer 1

我用这个方法，也解决问题了。
create table t_wh_tmpbasemployee as select * from basemployee;然后，在SQL中，把用到basemployee表的地方，全部替换成t_wh_tmpbasemployee 就不报那两个错了。不过，还是没有找到原因。

Answer 2

3113是一个继600外，另一个很难排查的错误code经常对这个错误的判断就是网络状况和实例里重要进程的毁坏。所以你首先从这两个方面着手，看看有没有问题。
1. 如果是客户机访问，确保网络通信状况一直OK，或者在服务机上执行一把看看。2. 检查session相关的trc文件的和日志文件，检查有没有一些异常的信息。根据这些信息再进一步排查。

Answer 3

1、网络肯定是没问题。因为我执行其它的SQL都没问题，就这个不行。再者，数据库和执行SQL的机器是一台机器。

Answer 4

这个问题我也碰到过，
后来检查是网络的问题，你可以确认下。
另外，不过是什么错误，你都可以看下trc文件来进行确定详细的。

Answer 5

对了,你的系统的空间情况如何?不是表空间.是指文件系统的情况.以及swap区域.

Answer 6

3113也是一个比较泛性的问题了，所以如果不是网络方面的问题的话，还是从日志文件和trc文件入手找到根本的原因。

Answer 7

是的，造成这个错的原因可能性有很多，一般应该不是网络的问题，因为楼主在执行sql后才会出现这个错误的。看log吧

Answer 8

最好是trace , 包括trace listener

Answer 9

另外，我把这个SQL分解成几段执行。在不用rownum判断的情况下，能够正常执行。如果加上全句（使用rownum判断的情况下），则要出03113\03114错误Select A.*, ROWNUM RN
          From (Select caseinfotable.ID,
                       caseinfotable.CASEID,
                       caseinfotable.INSURNO,
                       XZCLASS,
                       Insuredpeople,
                       ISREN,
                       ISWU,
                       Compensation,
                       Replacementsum,
                       MaintenanceTime,
                       PEOPLESUM,
                       ITEMSSUM,
                       RescueSUM,
                       otherSUM,
                       CASESUM,
                       TJDATE,
                       ISFZ,
                       ISJC,
                       basorganization.chiname as Commitmentdept,
                       COMPENSATIONCASENUMBER,
                       othernumber,
                       (Operators || basemployee.employeename) as operators,
                       operdept,
                       reportdate,
                       outcasedate,
                       (case
                         when casetype = 0 then
                          '待查勘'
                         when casetype = 1 then
                          '查勘完成'
                         when casetype = 2 then
                          '复勘完成'
                         when casetype = 20 then
                          '申请复勘'
                         when casetype = 3 then
                          '定损完成'
                         when casetype = 30 then
                          '打回定损'
                         when casetype = 4 then
                          '核损完成'
                         when casetype = 40 then
                          '核损上报'
                         when casetype = 5 then
                          '核价完成'
                         when casetype = 50 then
                          '核价上报'
                         when casetype = 6 then
                          '稽查完成'
                         when casetype = 7 then
                          '理算完成'
                         when casetype = 8 then
                          '核赔完成'
                         when casetype = 80 then
                          '核赔上报'
                         when casetype = 9 then
                          '核批完成'
                         when casetype = 10 then
                          '拒赔完成'
                         when casetype = 11 then
                          '销案完成'
                         when casetype = 12 then
                          '结案完成'
                         when casetype = 13 then
                          '撤案完成'
                         when casetype = 14 then
                          '赔案批改'
                         when casetype = 15 then
                          '不立案'
                       end) casetype,
                       (case
                         when peoplecasetype = 0 then
                          '待查勘'
                         when peoplecasetype = 1 then
                          '查勘完成'
                         when peoplecasetype = 2 then
                          '复勘完成'
                         when peoplecasetype = 20 then
                          '申请复勘'
                         when peoplecasetype = 31 then
                          '定损完成'
                         when peoplecasetype = 310 then
                          '打回定损'
                         when peoplecasetype = 41 then
                          '核价完成'
                         when peoplecasetype = 410 then
                          '核价上报'
                       end) peoplecasetype,
                       (case
                         when wucasetype = 0 then
                          '待查勘'
                         when wucasetype = 1 then
                          '查勘完成'
                         when wucasetype = 2 then
                          '复勘完成'
                         when wucasetype = 20 then
                          '申请复勘'
                         when wucasetype = 32 then
                          '定损完成'
                         when wucasetype = 320 then
                          '打回定损'
                         when wucasetype = 42 then
                          '核价完成'
                         when wucasetype = 420 then
                          '核价上报'
                       end) wucasetype,
                       casetimetable.A1,
                       casetimetable.A3,
                       casetimetable.A4,
                       casetimetable.A5,
                       casetimetable.A7,
                       casetimetable.A8
                  From CASEINFOTABLE,
                       basorganization,
                       basemployee,
                       casetimetable
                 where caseinfotable.commitmentdept =
                       basorganization.organizationcode
                   and caseinfotable.operators = basemployee.engname
                   And caseinfotable.caseid = casetimetable.caseid
                   And CaseType >= 1
                   and CaseType < 3
                   and caseinfotable.iscar = 1
                   And (COMMITMENTDEPT Like 'AEANOO' or
                       OPERDEPT Like 'AEANOO%' or SANGBAODEPT Like 'AEANOO%' or
                       soulidept Like 'AEANOO%' OR
                       COMMITMENTDEPT Like 'AEAAAL' or
                       OPERDEPT Like 'AEAAAL%' or SANGBAODEPT Like 'AEAAAL%' or
                       soulidept Like 'AEAAAL%' OR
                       COMMITMENTDEPT Like 'AEAAAH' or
                       OPERDEPT Like 'AEAAAH%' or SANGBAODEPT Like 'AEAAAH%' or
                       soulidept Like 'AEAAAH%' OR
                       COMMITMENTDEPT Like 'AEAC00' or
                       OPERDEPT Like 'AEAC00%' or SANGBAODEPT Like 'AEAC00%' or
                       soulidept Like 'AEAC00%' OR
                       COMMITMENTDEPT Like 'AEAD00' or
                       OPERDEPT Like 'AEAD00%' or SANGBAODEPT Like 'AEAD00%' or
                       soulidept Like 'AEAD00%' )
                 Order by CaseinfoTable.TjDate Desc) A

Answer 10

oracle不支持case...when...    最好用decode()

Answer 11

以下链接是跟踪文件，麻烦高人看一下，谢谢
http://www.hy11.com.cn/uploadfile/yai_ora_3448.txt

Answer 12

自己顶一下，难道大假其间，在牛们都不上CSDN了

Answer 13

注意观察磁盘I/O是否异常，很可能与I/O异常有关系

Answer 14

IO是用的radio5阵列，阵列卡显示状态是良好的，没有异常呀

Answer 15

看你的sql trace里
有这样的信息
*** 2009-09-30 13:48:22.484
ksedmp: internal or fatal error
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION] [kkfipbr+7] [PC:0x1D8E519] [ADDR:0x0] [UNABLE_TO_READ] []一个7445的信息，这里也是一个类似600的棘手错误号，我这里搜集了2中7445的解决方法，不过不见得适合你的错误，你试试先，
http://www.inthirties.com/thread-220-1-1.html
http://www.inthirties.com/thread-221-1-1.html不行的话，需要查找一下相关的metalink。

Answer 16

感谢inthirties，热心有实力的老鸟！！

Answer 17

看到了，是10201,把call stack帖到metalink上面去查一下看看是什么原因，我估计是个bug.另外，21楼的：oracle中有case when的。

Answer 18

是oracle10g
如果我把 case 的那几列去掉，问题依旧，还是要报错

Answer 19

我的 oracle 版本：10.2.0.1.0

Answer 20

经过多方面的测试，已初步确定是rownum引用起的。在数据库中，执行
select * from abc 这个句语很顺利，也是是0.0001ms的时间
但执行
select * from abc where ruwnum <=25 ，则至少要等上2秒以上的时间才能出结果

Answer 21

是你sql语句的问题。我也遇到的同样的问题，仔细检查发现sql的确存在问题。
把sql语句进行分解排查，一定能找到原因、、

Answer 22

oracle的bug也不少.类似这样的问题,在我们现在的项目发现多处,最终都是通过避开bug触发因素,或升级oracle补丁解决.如果对你自己写的代码有信心的话,不要对找到这类问题的发生原因过于执着,除非你想成为给oracle抓虫的专家,否则你只要找到问题的触发条件,避免在下一次开发的时候面临同一个问题.

Answer 23

哦?这个倒是挺奇怪的
SQL> select * from scott.emp where rownum<=14;     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM---------- ---------- --------- ---------- --------------- ---------- ----------    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80              800
        20      7499 ALLEN      SALESMAN        7698 20-FEB-81             1600        300        30      7521 WARD       SALESMAN        7698 22-FEB-81             1250        500        30
     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM---------- ---------- --------- ---------- --------------- ---------- ----------    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81             2975
        20      7654 MARTIN     SALESMAN        7698 28-SEP-81             1250       1400        30      7698 BLAKE      MANAGER         7839 01-MAY-81             2850
        30
     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM---------- ---------- --------- ---------- --------------- ---------- ----------    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81             2450
        10      7788 SCOTT      ANALYST         7566 19-APR-87             3000
        20      7839 KING       PRESIDENT            17-NOV-81             5000
        10
     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM---------- ---------- --------- ---------- --------------- ---------- ----------    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81             1500          0        30      7876 ADAMS      CLERK           7788 23-MAY-87             1100
        20      7900 JAMES      CLERK           7698 03-DEC-81              950
        30
     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM---------- ---------- --------- ---------- --------------- ---------- ----------    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81             3000
        20      7934 MILLER     CLERK           7782 23-JAN-82             1300
        10
14 rows selected.Elapsed: 00:00:00.10
SQL> select * from scott.emp;     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM---------- ---------- --------- ---------- --------------- ---------- ----------    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80              800
        20      7499 ALLEN      SALESMAN        7698 20-FEB-81             1600        300        30      7521 WARD       SALESMAN        7698 22-FEB-81             1250        500        30
     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM---------- ---------- --------- ---------- --------------- ---------- ----------    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81             2975
        20      7654 MARTIN     SALESMAN        7698 28-SEP-81             1250       1400        30      7698 BLAKE      MANAGER         7839 01-MAY-81             2850
        30
     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM---------- ---------- --------- ---------- --------------- ---------- ----------    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81             2450
        10      7788 SCOTT      ANALYST         7566 19-APR-87             3000
        20      7839 KING       PRESIDENT            17-NOV-81             5000
        10
     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM---------- ---------- --------- ---------- --------------- ---------- ----------    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81             1500          0        30      7876 ADAMS      CLERK           7788 23-MAY-87             1100
        20      7900 JAMES      CLERK           7698 03-DEC-81              950
        30
     EMPNO ENAME      JOB              MGR HIREDATE               SAL       COMM---------- ---------- --------- ---------- --------------- ---------- ----------    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81             3000
        20      7934 MILLER     CLERK           7782 23-JAN-82             1300
        10
14 rows selected.Elapsed: 00:00:00.10
另外 你的rownum写错了 呵呵

Answer 24

网络问题可能性比较大，ping 大包试试，看是不是丢包。

Answer 25

如果是丢包，或者怀疑丢包，我都建议您tracce listener 和client 端。再把trc放上来分析一下。