ORA-00600 when using SELECT UNION and PUBLIC SYNONYM

From: <awatkins1966_at_googlemail.com>
Date: Thu, 13 Nov 2008 05:19:39 -0800 (PST)
Message-ID: <cdb6c6b7-9159-4b04-8cc0-fbbf2dc013aa@b31g2000prb.googlegroups.com>

problem summary:

I am having problems with "PUBLIC SYNONYM" in UNION statement, which cause a crash, but if I rename the "PUBLIC SYNONYM" it works. Any ideas?

Full Question:
After an upgrade from 10g to 11g I get a ora-600 error when I do the following statement:

SQL> SELECT SF.STORE_ID, T.MONTH_OF_YEAR, SUM(SF.UNIT_SALES) AS TOT_UNIT_SALES_PER_MONTH
FROM ( SELECT * FROM SALES_FACT_1998

               UNION
               SELECT * FROM SALES_FACT_DEC_1998 ) SF, TIME_BY_DAY T

WHERE SF.TIME_ID=T.TIME_ID AND SF.STORE_ID=24 GROUP BY SF.STORE_ID, T.MONTH_OF_YEAR
HAVING SUM(SF.UNIT_SALES)>2500; The error reported is:
ERROR at line 2:
ORA-00600: internal error code, arguments: [kkqsrset-1], [], [], [],
[],
[],[], []

The SALES_FACT_1998 & SALES_FACT_DEC_1998 are PUBLIC SYNONYM.

The strange think is that if I create 2 new synonym with different names but pointing to the same table it works with no errors.

CREATE OR REPLACE PUBLIC SYNONYM NEW_SALES_FACT_1998 FOR FM_ADMIN.SALES_FACT_1998;
CREATE OR REPLACE PUBLIC SYNONYM NEW_SALES_FACT_DEC_1998 FOR FM_ADMIN.SALES_FACT_DEC_1998; And if I try to re-creating the original synonym, it still gives an error.

CREATE OR REPLACE PUBLIC SYNONYM SALES_FACT_1998 FOR FM_ADMIN.SALES_FACT_1998;
CREATE OR REPLACE PUBLIC SYNONYM SALES_FACT_DEC_1998 FOR FM_ADMIN.SALES_FACT_DEC_1998; Cheers..

Andrew Received on Thu Nov 13 2008 - 07:19:39 CST