Home » RDBMS Server » Server Administration » Tablespace fragmentation in 10g (Oracle 10.2.0.5.0 SE)
Tablespace fragmentation in 10g [message #529075] Fri, 28 October 2011 10:17 Go to next message
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

Re: Tablespace fragmentation in 10g [message #529080 is a reply to message #529075] Fri, 28 October 2011 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Any suggestions ?

Export, drop objects, reduce files, import.

Regards
Michel
Re: Tablespace fragmentation in 10g [message #529081 is a reply to message #529080] Fri, 28 October 2011 10:43 Go to previous messageGo to next message
jackypilou
Messages: 7
Registered: October 2011
Junior Member
Michel Cadot wrote on Fri, 28 October 2011 10:34
Quote:
Any suggestions ?

Export, drop objects, reduce files, import.

Regards
Michel


Yeah, I was trying to find something online but yes I suppose I have to export/import.

I still do not understand why it is not working with move/rebuilds...
Re: Tablespace fragmentation in 10g [message #529082 is a reply to message #529081] Fri, 28 October 2011 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because with LMT every extends (pieces of free space) are equal, there is no begin, no end.

Regards
Michel
Re: Tablespace fragmentation in 10g [message #529131 is a reply to message #529082] Fri, 28 October 2011 14:38 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Could it be that the tablespace contains dropped objects? If so, their extents won't be re-used. What happens if you purge the tablespace before doing the move and rebuild?
Re: Tablespace fragmentation in 10g [message #529641 is a reply to message #529131] Wed, 02 November 2011 04:44 Go to previous messageGo to next message
jackypilou
Messages: 7
Registered: October 2011
Junior Member
You mean, the recyclebin ?

If yes, I tried already, and recyclebin is empty.
Re: Tablespace fragmentation in 10g [message #535284 is a reply to message #529075] Tue, 13 December 2011 06:26 Go to previous message
vinodraj4u@gmail.com
Messages: 15
Registered: January 2010
Location: bangalore
Junior Member

try removing the table fragmentation.


Alter table <table_name> enable row movement;

alter table <table_name> shrink space;

and then try reducing the size.
Previous Topic: UNDO in parameter file
Next Topic: EXP_FULL_DATABASE role
Goto Forum:
  


Current Time: Fri Apr 19 13:42:18 CDT 2024