Re: Recovering space after delete

Richard Hansen wrote:
>
> On 5 Sep 1997 10:58:23 GMT, g.r.s.deisz_at_ptt-telecom.unisource.nl
> (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.
>
> I could be wrong here, but I believe that this will only create an
> initial extent the size of the all extents used by the table. It will
> not free up unused space in any extents. This unused space will
> simply be availabe for the same table, it won't be released for use by
> other segments which use the same tablespace.
>
> >
> >>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.
>
> The only way to free up the space for use by other segments (that I
> can think of right now) would be to export the table, recreate it with
> a small initial extent and next extent (and maybe pctincrease of 0)
> and import with ignore=y. It will fragment the table, but will ensure
> that a lot of the unused space is freed up. But I don't think this is
> what you'd want to do anyway if you are doing subsequent inserts. But
> this would be a good thing to do for any indexes on the table as
> deletes do not free up any space but rather retains it and hastens
> index stagnation.
>
> ===================================================
> Richard Hansen
> hansenrd_at_BITEME_SPAMMERS_cadvision.com
>
> To respond, remove BITEME_SPAMMERS_ from my address
> ===================================================

If you are running 7.3, then you can resize the size of your datafile, using command:

Alter database datafile <filename> resize <size>

amit
Oracle DBA/Unix SA Received on Mon Sep 08 1997 - 00:00:00 CDT