Oracle 中可以连接的表数量是否有限制？
我正在编写一个相当大的查询，我有 2 个表，内部连接和 15 个表左外部连接。当我添加 16 时，我得到ORA-03113：通信通道上的文件结束。如果我删除一个表以添加新表，则查询可以正常工作。
SQL Server 的查询运行良好，只是 Oracle 似乎遇到了困难。
I'm writing a fairly large query, and I have 2 tables, inner joined and 15 tables left-outer joined. when I add the 16, I get ORA-03113: end-of-file on communication channel. If I remove a table to add the new one, the query works fine.
The query runs fine for SQL Server, it's just Oracle that seems to be struggling.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
当客户端与数据库之间的连接意外中断时，会遇到 ORA-3113 错误。两个主要原因是：
如果您可以通过语句可靠地生成 ORA-3113，则属于第二种情况，很可能是 Oracle 的错误。当 Oracle 进程意外终止时，它会在 USER_DUMP_DEST 中留下一个跟踪文件（在 SQL*Plus
showparameter user_dump_dest中）。使用编辑器打开跟踪文件并查找其他更重要的错误消息（搜索ORA-）。您很可能会发现类似 ORA-7445 或 ORA-600 的服务器错误。在这种情况下，您需要打开 Oracle 支持 注释 ID 153788.1 以进一步确定问题及其解决方案。
ORA-3113 errors are encountered when the connection between the client and the database is interrupted unexpectedly. The two main reasons are:
If you can reliably produce an ORA-3113 with a statement, you are in the second case, most likely a bug from Oracle. When an Oracle process dies unexpectedly, it will leave a trace file in your USER_DUMP_DEST (in SQL*Plus
show parameter user_dump_dest). Open the trace file with an editor and look for other more significant error message (search forORA-).Most likely you will find a server error like ORA-7445 or ORA-600. In that case you will need to open the Oracle support note ID 153788.1 to further identify the problem and its solution.
我也遇到过这样的问题，它们与 Oracle 中的错误有关。
最近我没有遇到任何这些，可能是因为聘请的 DBA 对我们的数据库应用了许多补丁。
由于连接了 15 个表，因此不会出现此问题。我已经建立了连接更多的查询。我相信我编写的最大查询大约有 450 行，以多种不同的方式连接至少 50 个表。
有时，以不同的方式编写查询会起作用。您可以发布您的查询吗？我可能会认识到某种模式也给我带来了麻烦，并提出替代方案。
我想补充一点，查询的运行时间几乎不是造成这种情况的原因。我见过查询运行几分钟没有任何问题，而其他人则在几秒钟内就出现了这个问题。
I got problems like this too and they were related to bugs in Oracle.
Lately I haven't had any of these, probably due to the many patches applied to our database by a hired DBA.
This problem doesn't occur because of joining 15 tables. I've built queries joining a lot more. I believe the largest query I wrote was about 450 lines, joining at least 50 tables in many different ways.
Sometimes it works writing a query differently. Could you maybe post your query? I may recognise a certain pattern that caused me troubles too, and suggest an alternative.
I'd like to add that the running time of the query is hardly ever the cause of this. I've seen queries running for minutes without any problems, while others gave this problem in seconds.
Oracle 的各种限制可在附录 A< /a> 其数据库文档。我找不到任何专门针对查询中允许的联接数量的信息，但由于查询中允许最多 255 个子查询，所以我认为 16 个左联接不会超出它的能力。 编辑：
WHERE子句中允许有 255 个子查询。显然它是 无限制FROM子句但是，您列出的错误消息往往表明它是整个查询的实际长度：
The various limitations for Oracle are available in Appendix A of its database documentation. I can't find any specifically for the number of joins allowed in a query, but since you're allowed up to 255 subqueries in a query, I wouldn't think 16 left joins aren't beyond its abilities. Edit: you're allowed 255 subqueries in the
WHEREclause. Apparently it's unlimited in theFROMclauseHowever, the error message you've listed would tend to suggest it's the actual length of the query overall:
ORA-03113 意味着您的客户端应用程序已失去与 Oracle 影子进程的通信。因此，您可以先尝试不同的客户端工具。
当 Oracle 可执行文件被终止、终止或崩溃并且客户端连接未有序关闭时，也会发生 ORA-03113。您没有提到此后您必须重新启动 Oracle，所以我认为它与客户端或网络相关。否则你必须检查日志文件alert.ora等...
请放心，Oracle可以很好地处理15个以上的表...
ORA-03113 means that your client application has lost communication with the Oracle shadow process. So you might try a different client tool first.
ORA-03113 also occurs when the Oracle executable is killed, terminated or crashed and the client connections are not orderly closed. You didn't mention you've had to restart Oracle after that so I assume it's client- or network-related. Otherwise you'll have to check the logfiles alert.ora etc...
Please be assured that Oracle handles 15+ tables quite well...