Re: Oracle 9i insertion problem

Uwe Schneider <uwe_at_richard-schneider.de> wrote in message news:<3C8FBDCD.91E4387D_at_richard-schneider.de>...
> Vlad wrote:
> >
> > Hello,
> >
> > Here is the problem:
> >
> > table TEST1 has 1,000,000 rows inserted from a script
> > table TEST2 is:
> >
> > create table TEST2 (
> > FILE_ID NUMBER not null,
> > ATTRIBUTE_NAME VARCHAR2(255) not null,
> > ATTRIBUTE_VALUE VARCHAR2(255),
> > constraint pk_test primary key (FILE_ID, ATTRIBUTE_NAME),
> > constraint fk_test foreign key (FILE_ID)
> > references OTHER_TABLE (FILE_ID)
> > )
> > organization
> > index
> > storage
> > (
> > initial 100M
> > next 70M
> > )
> > pctthreshold 50
> > /
> >
> > which structure is identical to TEST1 (apart from organisation, TEST1
> > has conventional one)
> >
> > When I try to execute:
> >
> > insert /*+ APPEND PARALLEL(TEST2, 2)*/ into TEST2
> > select * from TEST1
> >
> > After several minutes of thinking Oracle comes up with the error:
> >
> > ERROR at line 1:
> > ORA-00600: internal error code, arguments: [ktfbbsearch-7], [125], [],
> > [], [],
> > [], [], []
> > both tables reside in dictionary managed tablespaces and all the
> > inserted data fit initial extent of TEST2.
> >
> > Could anyone help me out and tell where the problem is?
> >
>
> Obviously it is a bug in the software. Contact Oracle support.
>
> U.

I was triyng to understand what caused this problem and came to a conclusion:

The nature of the problem looks the same when you create a large object in LOCALLY MANAGED tablespace, i.e. if you have a tablespace with EXTENT MANAGEMENT LOCAL and you are trying to create a table with initial extent 30M it works fine (for me at least). If you go for a larger initial extent, say 60M Oracle comes up with the same error

ERROR at line 1:
ORA-00600: internal error code, arguments: [ktfbbsearch-7], [125], [], [], [], [], [], []

The only workaround I found so far is to create tablespace with EXTENT MANAGEMENT DICTIONARY. Oracle 9i uses UNDO tablespaces instead of rollback segments and those tablespaces can only manage extents LOCALLY, thus when you update large amount of data it may simply unable to create a segment in UNDO tablespace.

This at least explains why the same insert as select statement works fine for 300,000 rows.

Vlad. Received on Thu Mar 14 2002 - 05:02:42 CST