ora-03113：通讯通道的文件结束 -未解决

前几天同事写了一sql语句，说无法查询，刚开始还不信，以为是数据库连接中断，可自己试着查询了下，发现确实无法查询，一查询就提示：

ora-03113：通讯通道的文件结束 ， 查看警告日志发现有如下内容：

Errors in file d:\oracle\product\10.2.0\admin\telemt\udump\telemt_ora_3680.trc:
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION] [_kkoipt+2271] [PC:0x146707F] [ADDR:0x14] [UNABLE_TO_READ] []

在查看相应的跟踪文件telemt_ora_3680.trc部分内容如下:具体内容可见：/Files/lanzi/telemt_ora_3680.txt

 

Windows thread id: 3680, image: ORACLE.EXE (SHAD)
*** ACTION NAME:(SQL 窗口 - SELECT * FROM (SELECT) 2011-07-26 15:45:34.843
*** MODULE NAME:(PL/SQL Developer) 2011-07-26 15:45:34.843
*** SERVICE NAME:(telemt) 2011-07-26 15:45:34.843
*** SESSION ID:(144.113) 2011-07-26 15:45:34.843
*** 2011-07-26 15:45:34.843
ksedmp: internal or fatal error
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION] [_kkoipt+2271] [PC:0x146707F] [ADDR:0x14] [UNABLE_TO_READ] []
Current SQL statement for this session:
SELECT *
  FROM (SELECT ROW_.*, ROWNUM ROWNUM_
          FROM (select *
                  from (SELECT f.gwm_fid    id,
                               c.gwm_fid    fid,
                               a.eqp_type   eqpType,
                               a.spec_name  specialType,
                               a.eqp_name   eqpName,
                               c.name       containSite,
                               b.china_name containRoom
                          FROM rm_eqp           a,
                               rm_room_info     b,
                               i_exch_info      c,
                               i_exch_s         d,
                               p_street_surface f
                         WHERE sdo_relate(d.gwm_geometry,
                                          f.gwm_geometry,
                                          'MASK = ANYINTERACT') = 'TRUE'
                           AND a.room_id = b.room_id
                           AND b.station_id = c.station_id
                           AND c.gwm_fid = d.gwm_fid)
                 where id = '171') ROW_
         WHERE ROWNUM <= 10)
 WHERE ROWNUM_ >= 0

在网上搜到相关的资料，有的说是10gr2的bug，试着将数据库重启后刷新了共享池（刷新后共享池的大小变大了），但是仍无法查询。用dbv工具检测dbf文件也没有发现有逻辑坏块。

后来我将语句修改如下就可以查询了：

 

SELECT *
  FROM (SELECT ROW_.*
          FROM (select a.*, ROWNUM r
                  from (SELECT f.gwm_fid    id,
                               c.gwm_fid    fid,
                               a.eqp_type   eqpType,
                               a.spec_name  specialType,
                               a.eqp_name   eqpName,
                               c.name       containSite,
                               b.china_name containRoom
                          FROM rm_eqp           a,
                               rm_room_info     b,
                               i_exch_info      c,
                               i_exch_s         d,
                               p_street_surface f
                         WHERE sdo_relate(d.gwm_geometry,
                                          f.gwm_geometry,
                                          'MASK = ANYINTERACT') = 'TRUE'
                           AND a.room_id = b.room_id
                           AND b.station_id = c.station_id
                           AND c.gwm_fid = d.gwm_fid) a
                 where id = '171') ROW_
         WHERE r<= 10)
 WHERE r >= 0;

不知道是什么原因，难道真是bug？