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

On Nov 13, 4:19 pm, "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

Definitely a case for Oracle Support - MetaLink doesn't contain any publicly available information on any issues involving kkqsrset(), looks like it's a new defect or the upgrade went wrong and somehow damaged the dictionary. Be prepared to collect and upload trace dumps the server generated for this error.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Nov 13 2008 - 11:31:47 CST