Tablespace fragmentation in 10g [message #529075] |
Fri, 28 October 2011 10:17 |
|
jackypilou
Messages: 7 Registered: October 2011
|
Junior Member |
|
|
Hello,
I have to reduce the disk usage of the databases we have.
I have a 4gig tablespace, LMT/ASSM, and only 28meg are used !
Problem is I have some objects at the end of the tablespace.
Everytime I try to rebuild an index or ALTER TABLE move, the segment is put at the end despite the fact the space is indicated free in dba_free_space
Any suggestions ?
sql> select BLOCK_ID, BYTES, BLOCKS from dba_free_space where TABLESPACE_NAME='tbsp' order by 1;
BLOCK_ID BYTES BLOCKS
---------- ---------- ----------
3337 2124414976 259328
262793 529530880 64640
327561 319815680 39040
366697 65536 8
366713 1156710400 141200
507913 134152192 16376
6 rows selected.
sql> select segment_name, segment_type, block_id, bytes, blocks from dba_extents where tablespace_name='tbsp' order by 3;
SEGMENT_NAME SEGMENT_TYPE BLOCK_ID BYTES BLOCKS
------------------------- ------------------ ---------- ---------- ----------
[...]
my_index1 INDEX 366705 65536 8
sql> alter index my_index1 rebuild;
Index altered.
sql> select segment_name, segment_type, block_id, bytes, blocks from dba_extents where tablespace_name='tbsp' order by 3;
SEGMENT_NAME SEGMENT_TYPE BLOCK_ID BYTES BLOCKS
------------------------- ------------------ ---------- ---------- ----------
[...]
my_index1 INDEX 366697 65536 8
As you can see, my_index1 has moved, but is still far away in the tablespace.
Why not using the 1st free space, near block 3337 ?
any ideas ?
[Updated on: Fri, 28 October 2011 10:20] Report message to a moderator
|
|
|
|
|
|
|
|
|