Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Unanswered: intermittent ORA-03113's on same query

    Recently I started having problems with my Oracle database. Here is an example:

    SQL> select sum(CONSTRUCTION_BUDGET)
    2 from VW_ESTIMATE_INFO, VW_PROJ_AWD_INFO
    3 where VW_ESTIMATE_INFO.PROJ_ID = VW_PROJ_AWD_INFO.PROJ_ID
    4 and VW_PROJ_AWD_INFO.BUD_FISC_YR = '2002';
    select sum(CONSTRUCTION_BUDGET)
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel

    9 times out of 10, for some reason this query gives that error. I have no idea why, but occasionally it succeeds:

    SQL> select sum(CONSTRUCTION_BUDGET)
    2 from VW_ESTIMATE_INFO, VW_PROJ_AWD_INFO
    3 where VW_ESTIMATE_INFO.PROJ_ID = VW_PROJ_AWD_INFO.PROJ_ID
    4 and VW_PROJ_AWD_INFO.BUD_FISC_YR = '2002';

    SUM(CONSTRUCTION_BUDGET)
    ------------------------
    67109800

    There are also some other queries that seem to consistently succeed., like the following:

    select sum(proj_id) from proj_info;

    Can anyone provide some insight as to why I'm getting this return error code? Everything seemed to be going OK yesterday.

  2. #2
    Join Date
    Dec 2002
    Location
    Indonesia
    Posts
    6
    You can using ALIAS for your SQL:
    exp: select sum(a.CONSTRUCTION_BUDGET)
    from VW_ESTIMATE_INFO a, VW_PROJ_AWD_INFO b
    where a.VW_ESTIMATE_INFO.PROJ_ID = b.PROJ_ID
    and a.BUD_FISC_YR = '2002';

  3. #3
    Join Date
    Dec 2002
    Posts
    2
    Thanks for the reply Akumiling. It turns out the error is just the result of a very large join causing the server to run out of memory. The error message, along with the fact that the exact same query used to work just fine, led me to believe it was something else.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •