Fragmentation Problems
Date: Thu, 2 Apr 1992 07:25:39 GMT
Message-ID: <1992Apr2.072539.14769_at_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