Re: Fragmentation Problems

In article <1992Apr2.072539.14769_at_scammell.ecos.tne.oz.au>, brt_at_scammell.ecos.tne.oz.au (Brett Dunstan) writes:
|>
|> I have a small fragmentation problem with the ORACLE database
|>that is currently in use on a sparc 2. The result of the following
|>SQL query produced;
|>
|>SQL> select *
|>SQL> from user_free_space
|>SQL> order by block_id;
|>
|>
|>TABLESPACE_NAME FILE_ID BLOCK_ID BYTES
 BLOCKS
|>------------------------------ ---------- ---------- ----------

 9      


|>INDX                                    5         11      18432       


9     

  Most of the query results have been deleted.
|>...
|>
|> It seems odd that although many of the blocks are physically
|>next to each other Oracle cannot join them together. This may seem
|>a very easy question, but, is there a way of fooling Oracle to join
|>them together, either by creating and dropping tables, or any other
|>method.

The results of selecting from user_free_space are somewhat misleading. Oracle can join the blocks together, but does not until it needs to do so. If you create an object with large enough extents or if the next extent for a current object is of sufficient size, the joining of contiguous free space extents will occur. I believe Oracle adopted this strategy for performance reasons.
|>
|> Currently we drop the tablespace ( including its contents ),
|>re-create it and then re-insert the indexes or reimport the data in order
|>to remove any fragmentation ( ie: create one large extent ).

This is a different. Oracle will automatically join contiguous free space as the
as the need arises; however it cannot possibly join blocks of free space which are separated by an extent containing data.
|>
|> If possible please email me :-)
|>
|> brt_at_scammell.ecos.tne.oz.au
|>
|> Thanks in Advance

                                                                
                  Ian MacGregor [Stanford Linear Accelerator Center]