Re: Recovering space after delete

Amit Aggarwal wrote:
>
> 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

Hi,
I think the original question was how to release space from a table to be used by other tables when there have been many deletes.

If you are on 7.2 and above you can release unused space from a table back to the tablespaces free space. This only releases free space above the high water mark ( the highest point in a table taht was used).

So, first we have to reduce the high water mark then release the space. Try this:
1. export the table including indexes but with the statistics = none parameter and rows = y and indexes = y and compress = N.

2. truncate table. This removes all the rows in the table by setting the high water mark to 0 or first block.

3. Drop the indexes related to the table.

4. import the exported data using rows = y and indexes = n.

5. run another import with the parameters rows = N and indexfile = filename.

6. edit this file name to set the indexes to their proper sizes. Remove connect statements and rem statements.

7. use this edited file to rebuild the indexes.

8. give sql command alter table tablename deallocate unused keep integer;
set integer to a multiple of the block size to keep for growth (say 3 months or six months)

9. analyze table to compute statistics if you want cost based optimization.

By using truncate you set the high water mark down, and by not deleting the table you don't lose constraints, grants permissions, etc. One thing though if you have triggers that populate other tables when inserting to this table you will want to turn those triggers off during the import so the data is not duplicated in the other tables. After the import you can turn the triggers back on.

Hope this helps.
Dick Received on Fri Sep 19 1997 - 00:00:00 CDT