This discussion is archived
4 Replies Latest reply: Jul 6, 2012 12:43 AM by 926088 RSS

ORA-00600 when deleting a row with object column

926088 Newbie
Currently Being Moderated
Hi,

maybe someone around here can help me.

I have a table, a superclass and several derived classes.

the table definition looks like this:
/*-------------------------------------------------------------------------------------*/
/* table to store ObjectData for the current session/transaction */
/*-------------------------------------------------------------------------------------*/
create table SessionObjectRegistry(
     oid               number,
     transactid          varchar2(200),
     sessionid          number,
     hash               number,
     object               ObjectClass
);
-----------------------------------------
alter table sessionobjectregistry
add constraint pk_SessionObjectRegistry
primary key (oid);
-----------------------------------------
now i insert several objects, which can be instances of different classes.

if i then try to delete a row (or all rows) i receive an ORA-00600:
SQL> delete from sessionobjectregistry;
delete from sessionobjectregistry
            *
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkbnftn2], [], [], [], [], [], [], [], [], [], [], []
The db is
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
The error occurs on 11.2.0.3.0 - 64bit as well.

As a workaround i'm not deleting the rows, but setting the stored objects to null.
At the end of the routine i can truncate the table with the objects since truncating works.
I'm not the administrator of the database, therefore i may not change any settings.

Unfortunately i wanted to be able to share Objects between Sessions, which is possible with this
workaround (simply don't truncate at the end), but leaves a lot of crap lying around.

So any suggestion is welcome :)

Thank you!
  • 1. Re: ORA-00600 when deleting a row with object column
    KuljeetPalSingh Guru
    Currently Being Moderated
    I'm not the administrator of the database, therefore i may not change any settings.
    you should ask DBA to take a look or you may check this error in Metalink (MOS) "ORA-600/ORA-7445 Error Look-up Tool [ID *153788.1*] "

    As Ora-0600 is internal oracle error and you need to involve oracle support on this by raise SR.
  • 2. Re: ORA-00600 when deleting a row with object column
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    An ORA-0600 is an unhandled exception in the Oracle kernel. Often means that some kind of unexpected condition has been encountered and Oracle does not know how to deal with it. Could be due to a bug.

    On Metalink (now called My Oracle Support/support.oracle.com), note +Troubleshoot an ORA-600 or ORA-7445 Error Using the Error Lookup Tool [ID: 153788.1]+ can be used to troubleshoot an ORA-0600 crash.

    It however does not turn up anything for module kkbnftn2 for 11.1.0.7.

    A general search turned up an error/bug with module kkbnftn2 failing when dropping an object. From that bug, my guess is that your table could contain object columns of a child/sub class of ObjectClass that has been modified (or forcibly dropped), without cascading the change.

    This would result in the object column containing an invalid object value - and when attempting to delete that row, Oracle fails as that row has a messed up object column.

    But this is just a shot in the dark speculation.

    The correct approach for an ORA-600 is usually to file a SR (Service Request) with Oracle Support to investigate the crash and advise you on what the fix/workaround/patch is.

    You can however attempt to isolate this ORA-600 error. If it fails for a specific row, can you successfully select and display that row? What happens when you use a CTAS (Create Table As Select) to pull out that row into a brand new table and attempt to delete it from that table? If you first set that row's object column to null, can it then be deleted? Are any properties of that object column used elsewhere (e.g. as an index or constraint)? Etc.

    But Oracle Support would be the best option to get this problem resolved.
  • 3. Re: ORA-00600 when deleting a row with object column
    926088 Newbie
    Currently Being Moderated
    Thank you both,

    i already tried to look up this ORA-600, but as Billy mentioned, there's nothing to be found.

    Setting the object field to null doesn't help either, i tried this yesterday, after i came up with the mentioned work around.

    Maybe there's some corrupt data or object, i will have a look in this and come back in case i find something usefull.

    Thank you for now :)
  • 4. Re: ORA-00600 when deleting a row with object column
    926088 Newbie
    Currently Being Moderated
    Hi,

    i found the following:

    I create a super class similar to JAVAs Object class.
    I use a table with an object type column using the super class, so it can hold any of it's subtypes.

    Testrun starts here - Run 1 (The table and super class are always present and are not dropped before the run)
    I create subtypes of the object type used to define the column.
    I insert instances of the subtypes into the table, including additional data like an id a.s.o.
    I delete the rows during a procedure run, so the table is empty. // At this point deleting the rows is no problem.
    I drop the created subtypes after the test run using the validate option, making sure there are no left over dependencies.
    I start a new test run - Run 2 (The table and super class are always present and are not dropped before the run)
    I create subtypes of the object type used to define the column.
    I insert instances of the subtypes into the table, including additional data like an id a.s.o.
    I try to delete the rows during a procedure run again. // At this point i receive the ora-600.
    So it looks like oracle is keeping information about the subtypes in the table, even if there
    are no instances of them stored anymore and the subtypes don't exist anymore. And somehow these
    kept information don't cause any dependencies.

    if i change Run 2 the following way, it works again. (The table and super class are always present and are not dropped before the run)
    I create subtypes of the object type used to define the column.
    I insert instances of the subtypes into the table, including additional data like an id a.s.o.
    I commit everything (again, since the PL/SQL-block ended successfully which usually causes an automatic commit)
    I try to delete the rows by hand. // now all rows are deleted again
    I solved this with a workaround using an xmltype column instead of the object type column and
    updated my "put" and "get" routines to put xmltype (which is a walk in a park) and to retrieve the
    correct instance (which needs a little piece of dynamic sql - and is based on Billy's getClassName function posted in another thread).

    So i have now a nice workaround which by the way decouples the package and the table i use from the object types and
    "accidently" provides the functionality of having a serialized version (xmltype.getClobVal) of the object on hand.

    And finally my little object framwork is up and running, so i can handle objects in PL/SQL nearly the same way i can in Java :)

    Thank you

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points