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?
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.
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.
Click Here to Expand Forum to Full Width