Discover What's Possible

Table Fragmentation

March 20th, 2012 | Posted by in Administration | Scripts

Table Fragmentation

To get Actual, Optimal, Reclaimable Size from the tables of a specific user.

set linesize 200 trimspool on
col owner for a22
col table_name for a30
col tablespace_name for a20
select t.owner,t.table_name, s.tablespace_name,
round(s.bytes/1024/1024) actual_MB,
round((t.num_rows+1) * t.avg_row_len/1024/1024) optimal_MB,
round(s.bytes/1024/1024) -
round((t.num_rows+1) * t.avg_row_len/1024/1024) CLAIMABLE_MB
from dba_tables t, dba_segments s
where t.owner = s.owner and s.owner='&owner_name'
and  t.table_name = s.segment_name
and round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) > 200
order by CLAIMABLE_MB desc

Sample Output:-






From above screenshots, By perform Table reorganization 19gb, 11gb, 3gb …… Space can be reclaim from the tables.


You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

Leave a Reply

Your email address will not be published. Required fields are marked *

nine − = 1

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Get every new post on this blog delivered to your Inbox.

Join other followers:

Paste your AdWords Remarketing code here