Re: HELP: Freespace Defragmentation
Date: 1995/04/28
Message-ID: <3nr6ha$ik5_at_mother.usf.edu>#1/1
In article <1995Apr28.040406.23722_at_ml.csiro.au>, betlehem_at_ml.csiro.au (Andrew Betlehem) says:
>
>Is there any way to defrag the free space in an Oracle tablespace?
>I have 300 Meg freespace but only 20 meg in contiguous memory.
>
>Any help would be appreciatted.
>
>
>---
>___________________________________________________________________________
>Andrew.Betlehem_at_ml.csiro.au Database Analyst
>CSIRO Division Of Fisheries Pelagic Fisheries Resources
>Aus Tel: 002-325-332 Aus Fax: 002-325-000
>
>
>
What you do is map out you freespace using DBA_FREE_SPACE dictionary view by:
SELECT A.*, BLOCK_ID+BLOCKS END_BLOCK
FROM DBA_FREE_SPACE A
ORDER BY FILE_ID, BLOCK_ID;
Within each file (ie. same file_id) look a free extent and compare its
END_BLOCK column (BLOCK_ID+BLOCKS) to the BLOCK_ID in the next free extent. If
they are equal, you have 2 contiguous free extents. Do this through all the
free extents getting sizes of your big contiguous "chunks". Start creating
temporary tables sized to your "chunk" sizes going from largest to the
smallest. (Be aware that if you allocate all you free extents to
temporary tables, you have no space for "production" tables/indexes to
expand.) Once you have gone down in size to the smallest size "chunk"
you want to worry about, start droping your temporary tables going in order
of smallest to largest until all your temporary tables are dropped. You may
have to do a couple of passes at this. In V6 we had to play with some
of the init.ora parameters to allow us to consulidate a large number of
extents together for the creation of a table.
You also could buy a tablespace defragmenter. :-) Received on Fri Apr 28 1995 - 00:00:00 CEST