kfwebb_at_mindspring.com (KFWebb) wrote in message news:<2fd52da9.0411100722.a5ae3dd_at_posting.google.com>...
> Good day to all!
> Allow me to preface my question by stating that I am not a DBA,
> but a developer that has inherited admin responsibilities.
>
> Recently I had an issue with recurring ORA-01653 messages. Without
> going into the nasty details, the problem was finally solved when
> I moved the data to a different schema, truncated the tables that
> were generating the error messages and then moved the data back.
> This corrective action to reset the high water mark was clearly
> presented in c.d.o, and it worked as discussed.
>
> I completely understand the performance impact of the high water
> mark. My question concerns the notion that the tablespace is running
> out of space when there are blocks on the free block list. Being a
> developer, it would seem to me that there is a bug in Oracle when it
> clearly ignores the space that has been returned to the free block list.
> Using the example of 50,000 rows in a table followed by a deletion
> of 49,000 rows, the high water mark remains unchanged and table scans
> and other similar ops have to read up to the high water mark. This
> makes perfect sense; however, when I go to insert a new row I get a
> tablespace error.
>
> To draw an analogy to an operating system memory manager. In my mind
> if the memory manager worked the same way that Oracle manages its
> free space, once the RAM was filled and pages were marked by
> LRU logic to be placed in the queue for 'recycling' the least recently
> used memory pages would never be reused and the system would be rendered unusable.
>
> How come Oracle doesn't acknowledge/use available space in the free
> block list?
>
> Cheers,
>
> KFW
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 --
Received on Wed Nov 10 2004 - 14:42:48 CST
