--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th
"KFWebb" <kfwebb_at_mindspring.com> wrote in message
news:2fd52da9.0411111012.5c4528e6_at_posting.google.com...
> Mark.Powell_at_eds.com (Mark D Powell) wrote in message
> news:<2687bb95.0411101242.6301e04a_at_posting.google.com>...
>> kfwebb_at_mindspring.com (KFWebb) wrote in message
>> news:<2fd52da9.0411100722.a5ae3dd_at_posting.google.com>...
> --Version/Platform--
> Oracle version 8.1.7 running on Solaris
>
> In fact the table does have a LONG datatype, and a LOB as well. I
> know the LONG is deprecated, we want to migrate away from it,
> and Oracle is limited to one LOB type per table I believe.
One LONG, but multiple LOBs.
> resources. With that said I am a bit confused by your response. If
> a block has been released as a result of record deletion and placed
> on the free block list, and all remaining records have storage
> already allocated then why wouldn't the free space be available for
> new INSERTs that presumably require as much storage as the previously
> deleted records?
The problem comes at insert time - when you try to insert a row,
Oracle checks the top block on the freelist to see if the row will
fit without exceeding the 'pctfree' target for the block; if it can't,
the block is taken off the free list and the next block checked.
This happens a total of 5 times before Oracle shifts the high water
mark to allocate 5 new blocks
Unfortunately, if your LONGs are really long, then a row with a
long will always cause blocks to get knocked off the freelist like
this. It was partly for this reason that the LOB types were introduced.
Received on Thu Nov 11 2004 - 14:08:09 CST
