Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!not-for-mail
From: kfwebb@mindspring.com (KFWebb)
Newsgroups: comp.databases.oracle.server
Subject: Re: High Water Mark, Free Block List, ORA - 01653
Date: 11 Nov 2004 10:12:12 -0800
Organization: http://groups.google.com
Lines: 32
Message-ID: <2fd52da9.0411111012.5c4528e6@posting.google.com>
References: <2fd52da9.0411100722.a5ae3dd@posting.google.com> <2687bb95.0411101242.6301e04a@posting.google.com>
NNTP-Posting-Host: 4.20.179.250
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1100196732 4210 127.0.0.1 (11 Nov 2004 18:12:12 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 11 Nov 2004 18:12:12 +0000 (UTC)
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:229117

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>...
> > <snipped>
> > 
> > How come Oracle doesn't acknowledge/use available space in the free 
> > block list?
> > 
> 
> What version of Oracle on what platform?  By any chance does this
> table have a column of datatype long?  Oracle likes to insert long
> values in free blocks, i.e., blocks above the HWM.  If the table has a
> long column convert it to a LOB providing the system is 8.0+
> 
> HTH -- Mark D Powell --

Thanks for your reply Mark.

--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. We got
into this DATATYPE dilemma by the need to store more data in an 
existing schema while at the same time trying to leverage legacy 
applications without an expenditure of significant development 
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?