Fragmentation and defragmentation

[Bharath]

how to check fragmentation of a table/tablespace and how to defragment it.? Are there any procedures to do this.what are the views to check out?

[tycho]

Hi,

You can do a

select tablespace_name, bytes/1024/1024 from dba_free_space order by tablespace_name;

To defrag move/ rebuild all your objects to an other tablespace and coalesce and move / rebuild back to your initial tablespace.

Tycho

[sreddy]

When you rebuild,
Make sure you use initial extent, next extent sizes are equal and pctincrease set to zero. Alternatively you can use LMTs to avoid fragmentation.