4 Replies Latest reply: Mar 26, 2006 1:08 AM by 495612 RSS

    ORA-03113 End Of File on Communication Channel(Urgent)

    455014
      Hi All,

      I am getting this error when I execute a delete query which is having an In Clause and in the In Clause there is a derived table.

      Can anybody tell me what is the exact cause of the problem and resolution to the same? PFB the sample query used



      Ex: Delete From ABC

      Where (A,B) IN

      (Select A,B From ABC

      Inner Join

      (Select Sum(Z),X,Y

      FROM XYZ Group BY X,Y )TEST ON

      (ABC.A = TEST.X

      AND

      ABC.B = Test.Y))





      PS: Derived table needs to be used
        • 1. Re: ORA-03113 End Of File on Communication Channel(Urgent)
          Jes
          Nits,

          An ORA-03113 usually means you should contact support.
          • 2. Re: ORA-03113 End Of File on Communication Channel(Urgent)
            422218
            In my past experience, ORA-03113 occurs when the server process crashes. You could use a second session to check whether the first one still exists, or have a look in your alert log, or look for trace files.

            I notice that your query uses the INNER JOIN syntax. While I do like this addition to Oracle 9, I have occasionally had some strange behaviour that was solved by switching back to a non-ANSI join. Try re-writing your statement like this :-
            DELETE
            FROM abc
            WHERE (a,b) IN
            (
              SELECT a,b
              FROM   abc t2,
              (
                SELECT SUM(z),x,y
                FROM xyz
                GROUP BY x,y
              ) test
              WHERE t2.a = test.x
              AND   t2.b = test.y
            )
            or like this :-
            DELETE
            FROM abc
            WHERE (a,b) IN
            (
              SELECT x,y
              FROM xyz
            )
            Tak
            • 3. Re: ORA-03113 End Of File on Communication Channel(Urgent)
              499763
              In Oracle, a query that includes a subquery in the FROM clause, using GROUP BY can cause the database connection to break with an error ORA-03113. Try to create views and use them to do your processing while avoiding the above.

              Ben.
              • 4. Re: ORA-03113 End Of File on Communication Channel(Urgent)
                495612
                >>Can anybody tell me what is the exact cause of the problem and resolution to the same?
                As per the manual reference:
                ORA-03113 end-of-file on communication channel
                
                Cause: An unexpected end-of-file was processed on the communication channel. The problem could not be handled by the Net8 two-task software. This message could occur if the shadow two-task process associated with a Net8 connect has terminated abnormally, or if there is a physical failure of the interprocess communication vehicle, that is, the network or server machine went down. 
                
                In addition, this message could occur when any of the following statements/commands have been issued: 
                
                ALTER SYSTEM KILL SESSION ... IMMEDIATE 
                
                ALTER SYSTEM DISCONNECT SESSION ... IMMEDIATE 
                
                SHUTDOWN ABORT/IMMEDIATE/TRANSACTIONAL 
                
                Action: If this message occurs during a connection attempt, check the setup files for the appropriate Net8 driver and confirm Net8 software is correctly installed on the server. If the message occurs after a connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest an Oracle internal error that requires the assistance of Oracle Support Services.