Re: Table keeps growing inspite of purging

On Thu, 27 Nov 1997 16:48:11 -0800, Julio <julio.negueruela_at_si.unirioja.es> wrote:

>Thomas Kyte wrote:
>>
>> can you send more information. I ran a test in 7.1.6, 7.2.3, 7.3.3. All the
>> results were identical. (btw: all versions of the database release space, all of
>> them. None of them use a 'flag' delete or anything like that. That would be
>> dbase).
>>
>> In each database I created a table with 5,000 ~255 character rows. I then
>> analyzed the table and reported the free space/used space/allocated space. I
>> then repeatably deleted 500 rows, inserted 500 rows. I ran each test 2 times in
>> the database. Once analyzing after each insert/delete set and once analyzing
>> just at the beginning and the end.
>> the table hit a steady state right away. There must be something else going on
>> with your table. Could you tell us
>>
>> - what it looks like (indexes and all).
>> - how you insert into it.
>> - how you delete from it.
>>
>> I'd like to replicate your results if possible....
>
>I've been reviewing possible sollutions (I said it happened to me with
>the installation of Designer) and a probable cause is the free space
>fragmentation.
>Well one of the sollutions for Oracle 7.3 is to use the command
>alter tablespace <tablespace_name> coalesce
>This reduces the fragmentation in the tablespace.
>
>Regards

In earlier versions even though that ALTER TABLESPACE COALESCE syntax wasn't available, the database would still coalesce tablespaces. It happened automatically according to an algorithym that was applied when you tried to allocate an extent. The only "gotcha" that I remember was that the default PCTINCREASE for the tablespace had to be
> 0.

If you set PCTFREE to 0, and PCTUSED to 99 on a table, it ought to remain stable in size. Like I said earlier though, depending on the size of the table, you might get into performance problems due to free-list management.

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!