Re: High Water Mark, Free Block List, ORA - 01653

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>...
> > <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? Received on Thu Nov 11 2004 - 12:12:12 CST