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

On Nov 13, 10:35 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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.

I tried to delete all other PRIVATE SYNONYM but it did not make a difference.

Well I have now create a SR. Let see what Oracle say.

Cheers for now.

andrew Received on Fri Nov 14 2008 - 09:34:09 CST