Re: Table keeps growing inspite of purging

On Wed, 26 Nov 1997 12:45:04 -0800, Julio <julio.negueruela_at_si.unirioja.es> wrote:

>First of all, I apologize because my english isn't very good.
>
>> Sasha Ostojic wrote:
>>
>> I have an application that writes several hundred records to a table every
>> few minutes. There is a cron job that runs every hour that deletes records
>> older than two days from this table. This has been running now for about
>> three months. The problem is that I noticed that the table is growing over
>> time (the number of extents is growing). I would have expected it to reach
>> a certain size (after two days), and then the space freed by deleted records
>> would be reused. The table has one index, and it is still growing as well.
>>
>> The table is created with PCTFREE 5 and PCTUSED 60. The index is PCTFREE 5.
>>
>> Why is my table still growing?
>>
>> Thanks in advance.
>>
>> .sasha
>
>I don't know wich Oracle version you're using. Oracle doesn't eliminate
>the deleted data physically but they're marked as deletes. They've
>choosen between optimizing data saving and data acces the last oe.So
>they're still in the databases but they're not accesible, that's because
>you're table's still growing.
>
>I suggest to you every x-time (depending on the growing speed) to export
>table, drop it (it would be better to truncate it) and import it again.
>This way you eliminate deleted rows.
>
>Greetings,

Actually Oracle _does_ reuse space. It all depends on the percentage of used space in the block falling below the pctused threshhold. Once it does, that block will be placed on the free-list and becomes available for inserts. As long as it remains above pctused, the space is unavailable.

If you want to get that space back sooner, increase the pctused value. There is a trade-off though. The higher the pctused value, the more free-list management that has to go on, and performance will suffer some. Try setting up to about 85% and see how your system performs. This message delivered by electronic sled dogs! WOOF! Received on Wed Nov 26 1997 - 00:00:00 CST