更新语句导致 Oracle“没有更多数据可从套接字读取”？
我对表有一个有点简单的 UPDATE...WHERE EXISTS... 。然而，Oracle（通过所有其他客户端工具）立即（无延迟）返回 ORA-03113，表明可能存在连接问题，&c。我直接在数据库框上运行它。
该实例已启动、正在运行并且运行良好。我可以发出任何 SQL 和复杂的过程，并且数十种模式都可以运行。这只是一个特定的 SQL 语句。
可能是什么？如何找到？
$ sqlplus user/pass
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 28 13:04:38 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from dual;
D
-
X
SQL> update foo_a a
2 set a.field0 = (
3 select b.bar
4 from foo_b b
5 where b.custom_no = 0
6 and b.csf_id = a.id
7 ) , a.updated_on = current_timestamp
9 where exists (
8 select 1
10 from foo_b c
11 where c.custom_no = 0
12 and c.csf_id = a.id
13 );
update foo_a a
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
I have a somewhat simple UPDATE...WHERE EXISTS... to a table. Oracle (via all other client tools) however immediately (no delay) returns an ORA-03113 which indicates possible connection problems, &c. I am running this directly on the database box.
This instance is up, running, and happy. I can issue any SQL and complex procedures and dozens of schemas are operational. It's just this one particular SQL statement.
What could it be? How to find out?
$ sqlplus user/pass
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 28 13:04:38 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from dual;
D
-
X
SQL> update foo_a a
2 set a.field0 = (
3 select b.bar
4 from foo_b b
5 where b.custom_no = 0
6 and b.csf_id = a.id
7 ) , a.updated_on = current_timestamp
9 where exists (
8 select 1
10 from foo_b c
11 where c.custom_no = 0
12 and c.csf_id = a.id
13 );
update foo_a a
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Oracle 数据库很少真正崩溃。会话可能被终止或断开连接，但数据库仍保持运行。
这表明 Oracle 在处理查询时存在错误。通常，可以在跟踪文件中找到有问题的查询。有时警报日志中也会有有用的消息以及附加错误代码。如果您在跟踪文件中找到查询，有时您可以在 Metalink 中查找错误。在跟踪文件中，您可能会发现类似这样的行：
后面跟着 Oracle 错误，然后是导致问题的实际查询。对于您的情况，上述消息很可能会有所不同。只需在其中查找指示错误的消息即可。他们确实不难发现。
你能重写你的更新语句吗？这是测试环境吗？您能否消除 WHERE EXISTS 并用一个简单的表达式替换它，看看这是否是问题的根源？
Oracle databases very rarely truly crash. The session may be killed or disconnected, but the database stays up.
This has the earmarks of an Oracle bug in processing the query. Usually, the offending query can be found in the trace file. Sometimes there's a helpful message in the alert log as well, along with an additional error code. If you find the query in the trace file, sometimes you can look up the error in Metalink. In the trace file you might find a line like:
followed by an Oracle error and then the actual query causing the issue. The above message will quite likely be different in your case. Just poke around in there for messages indicating an error. They really aren't that hard to spot.
Can you rewrite your update statement? Is this a test environment? Can you eliminate the WHERE EXISTS and replace it with a simple expression just to see if that's the source of the problem?
这是一个非常常见的通信故障错误，可能由于多种原因（从网络问题到数据库崩溃）而发生。
检查这个以防万一给你一些对可能原因的想法。
That's a very generic error for communication failure that can happens for a myriad or reasons, from network problems to database crash.
Check this just in case to give you some ideas on possible causes.
ORA-03113 = 联系支持人员
他们会问的第一个问题：您是否已完全修补？
ORA-03113 = contact support
The first question they will ask: are you fully patched?