select statement give ora-03113 on Oracle 11

Question

When I run next sql statement on Oracle 11g I got ora - 03113 but same statement run normal on oracle 10gr2 can any one help me on this issue

    SELECT /*+ INDEX_JOIN(b) */
           b.child_
      FROM tab1 b
START WITH b.child_ IN (
     SELECT /*+ INDEX_JOIN(c) */
            c.id
       FROM tab2 c
      WHERE c.id IN (SELECT /*+ INDEX_JOIN(d) */
                            d.id
                       FROM tab3 d
                      WHERE d.id2 = 'X'
                     UNION
                     SELECT 'X'
                       FROM DUAL))
CONNECT BY b.parent_ = PRIOR b.child_

For those of us that don't know Oracle error codes off the top of our head, ORA-03113 is 'end-of-file on communication channel' (http://ora-03113.ora-code.com/). — Luke Woodward, Mar 20 '11 at 13:52
What have you tried so far? e.g. removing the hints, changing the UNION into a UNION ALL? — Jeffrey Kemp, Mar 21 '11 at 03:23

score 5 · Answer 1 · answered Mar 20 '11 at 13:54

ORA-03113 is one of Oracle's generic "server failure" errors. There should be a trace file in the USER_DUMP_DEST directory which contains diagnostic information. If you're unlucky there will be a core dump instead (in the CORE_DUMP_DEST directory). I say unlucky because it's harder to get a stack trace out of a core dump.

You may well need a DBA to help with access to these files and also in the interpretation.

ORA-03113 usually indicates a problem with the database's integrity, such as block or index corruption. So you may also need a DBA's assistance with resolving this matter.

In short, I think the people who voted to migrate this from ServerFault were wrong to do so.

I've known ORA-03113's come about from bugs in Oracle, so the OP's problem might not be a corrupt datafile. In such cases, the solution is to rewrite the query. Queries that cause this error usually have subqueries, so one thing to try is to rewrite the query using fewer subqueries. — Luke Woodward, Mar 20 '11 at 14:04

score 0 · Answer 2 · answered Jul 10 '13 at 16:52

I had the same problem with a query with Exists sub-query which support pagination. We identify the problem by re-writing the sub query to a simple one and then came a simple solution of changing the sub query select statement: instead of use tablename.*, we changed to only select the fields we need, tablename.id in our case. And the problem is solved.

Hope this helps.

select statement give ora-03113 on Oracle 11

2 Answers2