3

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.

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
Phillip Parker
  • 148
  • 2
  • 9
  • 2
    You might consider asking your question at http://dba.stackexchange.com/ – Gabe May 17 '11 at 07:06
  • Oracle absoutely has no problem joining only 17 tables. Are you using any exotic datatypes that might trigger an ORA-600? Or is there any block level corruption in the new table? – Gaius May 17 '11 at 09:26

4 Answers4

7

ORA-3113 errors are encountered when the connection between the client and the database is interrupted unexpectedly. The two main reasons are:

  1. A network problem has occured.
  2. The server process handling your request has been terminated

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 for ORA-).

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.

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
2

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.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
2

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 WHERE clause. Apparently it's unlimited in the FROM clause

However, the error message you've listed would tend to suggest it's the actual length of the query overall:

The limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
1

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...

HAL 9000
  • 3,877
  • 1
  • 23
  • 29