Re: Recovering space after delete

g.r.s. deisz wrote:
>
> In article <5uojbu$jml$1_at_news.enterprise.net>,
> "Oscar Ssentoogo" <impala_at_enterprise.net> wrote:
> >
> >We have large Oracle database tables.
> >We delete large amounts of records regardly, but the used tablespace area
> >seems to remain constant and not decrease after these deletes.
>
> I suppose that you also insert large amounts of records regularly? The space
> freed by deleting records will be used by new inserts.
> Maybe you can tune the pct_used parameter of your table: an oracle block will
> only be available for new inserts when the amount of space used in the block
> drops below pct_used. Setting pct_used too low will result in space wastage.
> Setting it too high, however, will result in frequent swapping of blocks to
> and from the free list, which may degrade performance.
>
> >Is there a way of recovering this space (i.e. can you compact the database
> >as in MS Access).
>
> You can export the data, delete the table and import it with the compress=yes
> option. Using the Enterprise Manager, this can be done by using the defragment
> option.
>
> >Or must you drop a tablespace file, delete this and recreate it to recover
> >the disk space?
>
> This is the most profound solution, I would see it as a last resort.
>
> Hope this helps.
>
> Stefan.
>
> ---------------------------------------------------------------------
> Name :G.R.S. Deisz
> Phone :+31-50-5855954
> E mail :G.R.S.Deisz_at_PTT-Telecom.Unisource.NL
> DISCLAIMER:This statement is not an official statement from, nor
> does it represent an official position of, PTT Telecom BV.

An alternative may be to "mark" records as "deleted" instead of physical deletes. Then update over those records - in effect limiting the DML to inserts and updates.
Not exactly pretty, but just another possibility.

Terry Received on Fri Sep 05 1997 - 00:00:00 CDT