ORA-03113: end-of-file on communication channel

[Ta Thi Thuy Ha]

After I execute a very complex SQL as follows:
SELECT A.K1, A.K2, B.K8, C.K9
FROM (A LEFT JOIN B ON
(A.K1 = B.K1 AND
A.K2 = B.K2 AND
A.K3 = B.K3 AND
((B.K4 = ' ' AND B.K5 <= A.K5) OR
(A.K4 = 'D' AND B.K5 = A.K5))))
LEFT JOIN C ON
(A.K1 = C.K1 AND
A.K6 = C.K6 AND
((C.K4 = ' ' AND C.K5 <= A.K5) OR
(A.K4 = 'D' AND C.K5 = A.K5)))
WHERE BITAND(A.K7, 8) = 0 AND
COALESCE(B.K5, '1')
=
CASE WHEN B.K5 IS NULL THEN '1'
ELSE
(
CASE WHEN A.K4 = ' ' THEN
(
SELECT MAX(B1.K5)
FROM B B1
WHERE (B1.K4 = ' ') AND (B1.K5 <= A.K5)
HAVING (B1.K1 = B.K1)
GROUP BY B1.K1
)
ELSE
(
CASE WHEN 'D' IN
(
SELECT B2.K4
FROM B B2
WHERE B2.K1 = B.K1 And B2.K5 = A.K5
) THEN A.K5
ELSE
(
SELECT MAX(B3.K5)
FROM B B3
WHERE (B3.K4 = ' ') AND (B3.K5 <= A.K5)
HAVING (B3.K1 = A.K1)
GROUP BY B3.K1
)
END
)
END
)
END AND
COALESCE(B.K4, '1')
=
CASE WHEN B.K4 IS NULL THEN '1'
ELSE
(
CASE WHEN A.K4 = ' ' THEN ' '
ELSE
(
CASE WHEN 'D' IN
(
SELECT B4.K4
FROM B B4
WHERE B4.K1 = B.K1 And B4.K5 = A.K5
) THEN 'D'
ELSE ' '
END
)
END
)
END AND
COALESCE(C.K5, '1')
=
CASE WHEN C.K5 IS NULL THEN '1'
ELSE
(
CASE WHEN A.K4 = ' ' THEN
(
SELECT MAX(C1.K5)
FROM C C1
WHERE (C1.K4 = ' ') AND (C1.K5 <= A.K5)
HAVING (C1.K1 = C.K1)
GROUP BY C1.K1
)
ELSE
(
CASE WHEN 'D' IN
(
SELECT C2.K4
FROM C C2
WHERE C2.K1 = C.K1 And C2.K5 = A.K5
) THEN A.K5
ELSE
(
SELECT MAX(C3.K5)
FROM C C3
WHERE (C3.K4 = ' ') AND (C3.K5 <= A.K5)
HAVING (C3.K1 = A.K1)
GROUP BY C3.K1
)
END
)
END
)
END AND
COALESCE(C.K4, '1')
=
CASE WHEN C.K4 IS NULL THEN '1'
ELSE
(
CASE WHEN A.K4 = ' ' THEN ' '
ELSE
(
CASE WHEN 'D' IN
(
SELECT C4.K4
FROM C C4
WHERE C4.K1 = C.K1 And C4.K5 = A.K5
) THEN 'D'
ELSE ' '
END
)
END
)
END;

it occurs error: ORA-03113: end-of-file on communication channel
in Trace file has error:
< internal="" or="" fatal="" error="">ORA-07445: exception encountered: core dump [kkessc()+2827] [SIGSEGV] [Address not mapped to object] [0x114] [] [] >

My database ORACLE 10g in LINUX has:
- SHARED_POOL_SIZE:472M
- DB_CACHE_SIZE: 392M

Please tell me method to solve this problem.
Thank you very much!

[Yogeeraj_1]

hi,

quite complex to troubleshoot without more information... You may wish to create a TAR on metalink.

regards
Yogeeraj

[Fred Ruffet]

Quite a good query :)

Having an ora-3113 client side and an ora-7445 or ora-600 server side, you must open a TAR on metalink to have support from Oracle.

7445 and 600 errors are not easy to qualify and that may correspond to a bug.

Regards,

Fred