Re: ORA-00600 when using SELECT UNION and PUBLIC SYNONYM

From: joel garry <joel-garry_at_home.com>
Date: Thu, 13 Nov 2008 14:35:16 -0800 (PST)
Message-ID: <2f8306ab-e4d6-455e-b663-db182ba2c46d@k1g2000prb.googlegroups.com>


On Nov 13, 5:19 am, "awatkins1..._at_googlemail.com" <awatkins1..._at_googlemail.com> wrote:
> 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

This is all wild speculation but... Do you have a local object with the same name as the problem objects? That is supposed to take precedence, perhaps you have some additional issues such as deletion not removing privilege properly, perhaps due to the fix for bug 3650346. Look at everything you can about these things from dba_objects, including any related recyclebin objects.

Agree with the others, support ought to be very interested if you can come up with a replicable ora-600 situation.

jg

--
@home.com is bogus.
“please list all aliases or 'handles' you have used to communicate on
the Internet.” - Application for high-ranking job in Obama
administration.
Received on Thu Nov 13 2008 - 16:35:16 CST

Original text of this message