Re: sqlldr locks fet$ and uet$, blocks smon, 100% cpu usage

"Don Seiler" <don_at_seiler.us> wrote in message news:pan.2003.09.10.00.32.51.819498_at_seiler.us...
> All the fields in this table are type varchar2 and number, no long or
> long raw. Thanks!
>
> Don.

It goes a bit further than that.

If your table definition read:

COL1    varchar2(4000)
COL2    varchar2(4000)
COL3    varchar2(4000)

....then it is still almost certain to be row chaining, and not migration. It's the row length that's the issue, not just whether there are LONGs and/or LONG RAWs present.

Regards
HJR
> On Wed, 10 Sep 2003 09:27:40 +1000, Howard J. Rogers wrote:
>
> > You need to determine whether it is row chaining or row migration (the
> > relevant column in DBA_TABLES is CHAIN_CNT... which covers both
> > eventualities, despite its name).
> >
> > The way to tell the difference is to look at the table definition. If
you
> > "desc blah', and see:
> >
> > COL1 CHAR(2)
> > COL2 NUMBER(8,2)
> > COL3 VARCHAR2(30)
> >
> > ....then the row-length in this table is about 40 bytes long, give or
take,
> > and there is no way this can be row chaining (which is, as you say, when
the
> > row length exceeds the database block size). It must be row migration.
> >
> > On the other hand, if you saw:
> >
> > COL1 LONG
> > COL2 LONG RAW
> >
> > etc etc etc, then you are dealing with row lengths potentially in the
> > gigabytes, and that's definitely going to be row chaining. About which
you
> > can do sod-all, short of re-creating your database with a bigger block
> > size... but last time I checked, Oracle did not permit block sizes to be
in
> > the gigabyte range!
> >
> > If it's migration, then 'alter table blah pctfree X' (where X is bigger
than
> > it is now) will help prevent future migrations. But it won't fix up
anything
> > that's already migrated. The only way to do that is to somehow arrange
for
> > the affected rows to be re-inserted into the table, and the easiest way
of
> > doing that is 'create table blahcopy as select * from blah', 'truncate
table
> > blah', insert into blah select * from blahcopy', 'drop table blahcopy'.
> >
> > Which happens to be one of the same solutions I mentioned in my reply to
> > your other post. So if you are fixing up tablespace fragmentation, you
can
> > fix up row migration at exactly the same time, for free.
> >
> > Regards
> > HJR
>
Received on Tue Sep 09 2003 - 19:48:19 CDT