Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: Fragmentation Problems

Re: Fragmentation Problems

From: Ian A. MacGregor <ian_at_unixhub.SLAC.Stanford.EDU>
Date: 2 Apr 92 15:21:08 GMT
Message-ID: <3615@unixhub.SLAC.Stanford.EDU>

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
|>------------------------------ ---------- ---------- ----------



|>INDX 5 2 18432
 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]
Received on Thu Apr 02 1992 - 09:21:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US