Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.wirehub.nl!news.iesy.de!troll.iesy.de!news.uni-stuttgart.de!carbon.eu.sun.com!btnet-feed5!btnet!news.btopenworld.com!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: High Water Mark, Free Block List, ORA - 01653 Date: Thu, 11 Nov 2004 20:08:09 +0000 (UTC) Organization: BT Openworld Lines: 55 Message-ID: References: <2fd52da9.0411100722.a5ae3dd@posting.google.com> <2687bb95.0411101242.6301e04a@posting.google.com> <2fd52da9.0411111012.5c4528e6@posting.google.com> NNTP-Posting-Host: host217-43-69-185.range217-43.btcentralplus.com X-Trace: sparta.btinternet.com 1100203689 25100 217.43.69.185 (11 Nov 2004 20:08:09 GMT) X-Complaints-To: news-complaints@lists.btinternet.com NNTP-Posting-Date: Thu, 11 Nov 2004 20:08:09 +0000 (UTC) X-RFC2646: Format=Flowed; Original X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MSMail-Priority: Normal X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:229131 -- 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" wrote in message news:2fd52da9.0411111012.5c4528e6@posting.google.com... > Mark.Powell@eds.com (Mark D Powell) wrote in message > news:<2687bb95.0411101242.6301e04a@posting.google.com>... >> kfwebb@mindspring.com (KFWebb) wrote in message >> news:<2fd52da9.0411100722.a5ae3dd@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.