Oracle 9i strange behavior (ORA-600)

From: TECNISA Development Team <desenvolvimento_at_tecnisainfor.com.br>
Date: 27 May 2002 06:50:23 -0700
Message-ID: <344fb668.0205270550.19150f48@posting.google.com>

Hi everyone!

I'm facing a strange behavior on Oracle 9i. We have a SELECT statement with subqueries, that when we ran it against the Oracle Database from one of our clients we got an ORA-00600 error!

I thought it could be some problem on the database, server, OS, but another client got the same error on the same statement.

We have an Oracle 9.0.1.0.0 running in a Linux box, our clients have 9.0.1.0.2. We cant reproduce the error on our server.

The DBA from the first client said that the problems was on the UNION ALL that exists on the subquery. When I take it off the problem stops, but my performance goes down!!! He suggested including a ROWNUM > 1000000000000000 on the statement to fix the error. But I didn't like this solution.

This is an example of the SELECT statement:

SELECT FIELD, FIELD, FIELD,
FROM
   TABLE1, TABLE2,
   (SELECT FIELD, FIELD
    FROM TABLE3     UNION ALL     SELECT FIELD, FIELD
    FROM TABLE4
    WHERE NOT EXISTS (SELECT FIELD, FIELD

                      FROM TABLE5)

    UNION ALL     SELECT FIELD, FIELD
    FROM TABLE6
    WHERE NOT EXISTS (SELECT FIELD, FIELD

                      FROM TABLE7))

WHERE
    CONDITIONS... Can anybody help me? Does anyone know why this is happening?

Thanks a lot!!!!

Zed. Received on Mon May 27 2002 - 08:50:23 CDT