Discover What's Possible
Header

Table Fragmentation

March 20th, 2012 | Posted by nassyambasha@gmail.com 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.

Add Comment Register



Leave a Reply

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


+ 9 = thirteen

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>

Follow

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

Join other followers:

Paste your AdWords Remarketing code here