Search code examples
sqldatabaseoracle-databasejoinora-03113

Are there limits to the number of tables that can be joined in 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.

Solution

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