ORA-03113: 通信通道的文件结束

我执行了一个存储过程，      出现如下提示：  
ORA-03113:      通信通道的文件结束

这是什么意思？  
---------------------------------------------------------------  

这个多半是由于网络问题引起的      ,本身不是oracle的错误,检查一下网络状况和系统核心参数的设定  
---------------------------------------------------------------  

“通信文道结束”是个含义甚宽泛的错误，它仅仅是oracle的客户端前台进程（(svrmgrl,      sqlplus,   
RMAN,      Oracle      Forms      etc.)      与其后台进程丢失了连接。  
错误的原因很多。在oracle开发者提到这个词的时候，总以为是网络故障，但是据报告，任何造成后台进程crash的事情都是出现这个错误的原因。  

该信息或许和其他的错误信息一同出现。如果被损坏的后台进程是oracle的关键进程（PMON,      SMON,      DBWR       etc.），你会得到“oracle不可用”的错误信息。查看你的日志或者查看任何跟踪文件可以帮助你对问题进行诊断。  

下面是一些造成该错误的原因总结：  
（1）Invalid      value      for      processes      parameter      in      init.ora      when      starting      database  
       当然，要解决它，就是把那些参数值调整为有效范围内的值。  
（2）setuid      bit      needs      setting      on      the      oracle      executable(Unix      only).  
The      oracle      executable      in      the      $ORACLE_HOME/bin      directory      should      have      the      permissions:    
         -rwsr-sr-x          1      oracle          dba  
（3）shared_pool_size      too      small.  
       你可以把它设置在200M以上。  
（4）timed_statistics=true.  
         你可以把它设置为false。  
（5）An      insert      into      a      table      which      grew      into      an      additional      extent      allocated      in      a      datafile       which      was      over      2Gb      in      size.  
       这是在你的操作系统文件不允许超过2G的时候才会发生的，而且如果发生了错误，数据库就完蛋了。
（6）Create      table      as      select      ....      where      there      was      an      invalid      procedure      or      function      called      from      a      trigger      defined      on      the      table      we      were      selecting      from.  
那就是把你所有的过程、函数和包全部编译一遍。  
（7）Make      use      of      Oracle      Support      /      Metalink  
       寻求oracle技术支持吧！

不过通过以上的建议没有解决问题，然后又往下搜

ORA-03113 And ORA-03114 On Desktop Platforms

What does ORA-03113 "end-of-file on communication channel" really mean?    

ORA-03113 is the most common catchall error.    It basically means that  
communications were lost for an unexpected reason.    It is usually followed by  
ORA-03114 "not connected to ORACLE".    

1.    As it turns out, the most common reason is that the Oracle shadow process
      on the server died unexpectedly.    So, if a running process were to  
      suddenly encounter an ORA-03113 and/or 3114, the first place to check is  
      the alert.log on the server to see if any other Oracle errors occurred.  

2.    Next most common cause of ORA-03113 is that the SQL*Net driver was not  
      linked into the Oracle executable on Unix.    While the listener             
     successfully received the connection and passed it to the Oracle shadow  
      process, the shadow process would fail to respond in any way because it
      would not know how to.    So the client will see an ORA-03113 at connect     
     time.   

3.    Third most common cause is a machine crash or network failure at the  
      server side.   

4.    One less common cause has been observed is when there are two servers with
      the same node names on the same network.  

5.    ORA-03113 has also been noticed where the a TOKEN RING card has the  
      Shared RAM size set to 8KB rather than 16KB.    If you are using a    TOKEN  
      RING card check the shared buffer size and try increasing it.   

6.    ORA-03113 also occurs when INIT.ORA parameters CONTEXT_AREA and   
      CONTEXT_INCR are set to a value of 4096.    Increasing the value to 8192  
      resolves the 3113.    (RDVMS V6 only)  

7.    ORA-03113 also occurs when there are duplicate IP addresses on the  
      network. To find the duplicate addresses turn off the unit that is getting
      the 03113 and ping its IP address.    If the ping responds then you have to
      find the offending unit.  

8.    If an ORA-03113 error occurs intermittentlly on comparatively large select
      statements through SQL*DBA for OS/2 or when performing a query through  
      SQL*DBA, try setting the default buffer size to 4096, especially if   
      issuing an order by causes no problems but going through Q&E or SQR hangs  
      the machine.  

9.    If using FTP V2.11 and running a large SQL*Plus script with over 38        
     variables of mostly character data types, check the lan drivers.    The dxmc
      driver loads addressing for token ring adapter has an option to save  
      memory (through IBM lan suppport program).  

          r parameter (r: reduced)  
          t parameter (t: tiny)  

      You must specify the r and t parameter for the dxmc drive.  

      It all comes down to the fact that the client went out to read some  
      information from the server and suddenly found out the connection was no  
      longer there.  

      ORA-03113 is just a symptom of a larger problem that will require more  
      diagnosis to track down.    Hopefully, the above information will lead you
      in the right direction to find the solution.  

      To debug an ORA-03113, it is a good idea to attempt the same operation  
      while doing a loopback, i.e can any tool on the server connect using the  
      same connect string as they specify from the Desktop client?    If the same  
      problem occurs doing a loopback, then you know the problem resides on the
      server side and not on the Desktop client side.    

      To perform a loopback invoke SQLPLUS or SQLDBA from the server, and at the
      SQLPLUS or SQLDBA prompt on the server type:  

           CONNECT USERNAME/PASSWORD@t:<servername>/<portnum>:<sid>   

      For example, if you are getting an ORA-03113 issuing a certain query from
      SQL*Plus on DOS while connected via SQL*Net TCP/IP to a Unix server, try  
      a loopback by invoking SQL*Plus on the Unix machine and use the same  
      "t:<servername>:<sid>" connect string and then issue the same SELECT  
      statement.  

10. This error is sometimes received intermittently with applications written  
      with Gupta SQLWindows.    Apparently, the problem is due to some buffer  
      being used by the Gupta product.    This buffer is used to fetch data  
      returned by a query on the client side.    The default value for a parameter
      called FETCH ROW (in SQLWINDOWS) is 20.    Lowering this to something like  
      15 seems to work fine.
系 SQL 语句问题,可以 SQL_TRACE 去跟踪一下.

有的时侯要对Oracle进行relink操作。
在Unix上通过cp拷贝安装的时候，常常会出现环境变量的问题，和个别执行程序连接问题。 LD_LIBRARY_PATH如果设置的不正确会导致问题，在这种情况下，需要对Oracle进行relink。如果可执行文件oralcle被破坏，也要对其relink。 如果安装了并行服务器选项而Distributed Lock Manager没有安装或正确运行也会导致错误。

• 客户端通信不能正确处理

1.SQL*Net驱动器的问题：

如果使用的版本比较低的驱动器，请更换到新版本的驱动。 SQL*Net 的驱动没有连接到Oracle可执行文件会导致错误。

2.检查TCP/IP网络是否通畅；
3.Windows平台的常见网络问题：

在Windows平台创建数据库的时侯,如果出现该问题可以考虑用如下的方法：

首先检查本地网络设置.查看网络上是否有同名的结点或有冲突的IP.如果问题依旧,可以保守的用下面的方法：

1). 禁用网卡：将本地连接状态改为禁用;
2). 将sqlnet.ora文件打开(以记事本形式)将nts验证注释掉：

#SQLNET.AUTHENTICATION_SERVICES= (NTS) 

3). 创建数据库;
4). 创建成功后,恢复本地连接；

• 数据库服务器崩溃/操作系统崩溃/进程被异常的Kill

在连接过程中，如果Oracle数据库的服务器崩溃或者数据库所在的操作系统崩溃，就会出现这 个错误，Oracle Server崩溃的原因可能因为主要后台进程死掉，被错误的进行了Kill操作。如果是这个原因还是比较容易解决的。此外,和OS有关的应用程序存在内存泄漏(或者有病毒)的时侯也会导致Oracle后台程序问题。 推荐排错步骤：

• 1、 查看应用软件相关进程是否正常运行；
• 2、 查看有无内存泄漏；
• 3、 查杀病毒；
• 4、 确定系统管理员没有进行误操作；
• 5、 确定无黑客入侵行为；
• 6、 其它不确定因素．．．．．．

• Oracle 内部错误 / Bug

如果查看background_dump_dest目录中的alert.log发现有ora-600/ora-07445等错误,可以到Metalink站点上查看具体信息及其解决方案。一般情况下要打软件补丁。

• 特定SQL、PL/SQL引起的错误

尝试把SQL进行分开执行,也可以用SQL_TRACE来进行跟踪,找到导致问题的SQL语句。在SQLPlus下：

ALTER SESSION SET sql_trace=TRUE;

SQL语句中的非法字符和不合理的处理结果，甚至一些不可解释的原因偶尔会带来问题.
SQL问题举例：

SELECT *
   FROM (SELECT ROWNUM AS num, k.*
           FROM (SELECT    a.cp_code, c.cp_cha_name, a.service_code,
                          a.service_name, a.content_name,
                          SUBSTR (a.access_time, 1, 8) thedate,
                          COUNT (*) AS hit_count
                     FROM sm_wap_log_daily_tab a, t_cp_info c
                    WHERE (SUBSTR (a.access_time, 1, 8) BETWEEN '20040301'
                                                            AND '20040304'
                          )
                      AND c.cp_code LIKE '%%'
                      AND a.cp_code = c.cp_code
                      AND a.service_code LIKE '%%'
                 GROUP BY a.cp_code,
                          c.cp_cha_name,
                          a.service_code,
                          a.service_name,
                          a.content_name,
                          SUBSTR (a.access_time, 1, 8)
                 ORDER BY a.cp_code,
                          a.service_code,
                          a.content_name,
                          SUBSTR (a.access_time, 1, 8) DESC) k) n;

上面这条语句在9204/Linux 系统上始终出现03113 的错误。对语句进行细化，分成小一点的子语句逐步执行，最后判定问题出现在

                  ORDER BY a.cp_code,
                          a.service_code,
                          a.content_name,
                          SUBSTR (a.access_time, 1, 8) DESC) k) n;
       

中的 SUBSTR (a.access_time, 1, 8) 这里。去掉SUBSTR (a.access_time, 1, 8)则问题不再出现。尝试调整SUBSTR (a.access_time, 1, 8) 的位置，语句得到通过。之后，顺便优化一下该语句。:)　

SELECT *
   FROM (SELECT ROWNUM AS num, k.*
           FROM (SELECT    a.cp_code, c.cp_cha_name, a.service_code,
                          a.service_name, a.content_name,
                          SUBSTR (a.access_time, 1, 8) thedate,
                          COUNT (*) AS hit_count
                     FROM sm_wap_log_daily_tab a, t_cp_info c
                    WHERE (SUBSTR (a.access_time, 1, 8) BETWEEN '20040301'
                                                            AND '20040304'
                          )
                      AND c.cp_code LIKE '%%'
                      AND c.cp_code = a.cp_code
                      AND a.service_code LIKE '%%'
                 GROUP BY a.cp_code,
                          c.cp_cha_name,
                          a.service_code,
                          a.service_name,
                          a.content_name,
                          SUBSTR (a.access_time, 1, 8)
                 ORDER BY (SUBSTR (a.access_time, 1, 8)),
                          a.cp_code,
                          a.service_code,
                          a.content_name DESC) k) n;

• 系统空间不够

任何时侯都要确保数据库系统有足够的空间.如果 USER_DUMP_DEST和BACKGROUND_DUMP_DEST没有剩余空间的话,会导致此问题.此外,如果打开了审计,AUDIT目录要由足够的空间.如果激活了Trace的话,Trace目录要由足够的空间. Dave Wotton的文档 (Local Copy) 表明,在对表进行插入数据的时侯,如果文件超过了2G (而文件系统有2G限制),会导致该问题.

• 防火墙的问题

如果数据要通过防火墙，请联系系统管理员，询问是否对数据库数据进行了过滤或者是突然禁止了通信端口。如本地安装有个人防火墙，请检查本地设置。

• 其它方面说明

导致这个错误的原因有很多种，上面列到的只是一些典型情况。经常去一些数据库技术论坛可能会有帮助。比如说ITPUB( http://www.itpub.net)、CNOUG(http://www.cnoug.org)等。