我执行了一个存储过程， 出现如下提示：
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)等。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
