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

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

Fragmentation Problems

From: Brett Dunstan <brt_at_scammell.ecos.tne.oz.au>
Date: Thu, 2 Apr 1992 07:25:39 GMT
Message-ID: <1992Apr2.072539.14769@scammell.ecos.tne.oz.au>

        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      
INDX                                    5         20      16384          8      
INDX                                    5         28      18432          9      
INDX                                    5         37      10240          5      
INDX                                    5         42      20480         10      
INDX                                    5         52      10240          5      
INDX                                    5         57      57344         28      
INDX                                    5         85      57344         28      
INDX                                    5        113      86016         42      
INDX                                    5        155     129024         63      
INDX                                    5        218      51200         25      
INDX                                    5        243      51200         25      
INDX                                    5        268      77824         38      
INDX                                    5        306     116736         57      
INDX                                    5        363       4096          2      
INDX                                    5        365       4096          2      
INDX                                    5        367       4096          2      
INDX                                    5        369       4096          2      
INDX                                    5        371      14336          7      
INDX                                    5        378      14336          7      
INDX                                    5        385      14336          7      
INDX                                    5        392      16384          8      
INDX                                    5        400       4096          2      
INDX                                    5        402       4096          2      
INDX                                    5        404     186368         91      
INDX                                    5        495     186368         91      
INDX                                    5        586     280576        137      
INDX                                    5        723     421888        206      
INDX                                    5        929     198656         97      
INDX                                    5       1026     198656         97      
INDX                                    5       1123     299008        146      
INDX                                    5       1269     448512        219      
INDX                                    5       1488     260096        127      
INDX                                    5       1615     260096        127      
...

        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.

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

        If possible please email me :-)

                                        brt_at_scammell.ecos.tne.oz.au

                                        Thanks in Advance Received on Thu Apr 02 1992 - 01:25:39 CST

Original text of this message

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