Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!logbridge.uoregon.edu!news.maxwell.syr.edu!news1.optus.net.au!optus!spool01.syd.optusnet.com.au!spool.optusnet.com.au!not-for-mail From: "Howard J. Rogers" Newsgroups: comp.databases.oracle.server References: <3f5e6267$0$14560$afc38c87@news.optusnet.com.au> Subject: Re: sqlldr locks fet$ and uet$, blocks smon, 100% cpu usage Date: Wed, 10 Sep 2003 10:48:19 +1000 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Lines: 85 Message-ID: <3f5e7550$0$563$afc38c87@news.optusnet.com.au> NNTP-Posting-Host: 203.164.6.15 X-Trace: 1063155024 563 203.164.6.15 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242751 "Don Seiler" wrote in message news:pan.2003.09.10.00.32.51.819498@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 >