tablespace fragmentation

select
b.file_name, b.tablespace_name,
nvl(round(sqrt(max(a.blocks)/
sum(a.blocks))*(100/sqrt(sqrt(count(a.blocks)) )),2),0)
fragmentation_index,
decode(c.inc,null,’no’,’yes’) autoextend,
count (*) free_chunks,
decode (
round ((max (a.bytes) / 1024000), 2),
null, 0,
round ((max (a.bytes) / 1024000), 2)) largest_chunk
from
sys.dba_free_space a,
sys.dba_data_files b,
sys.filext$ c
 where
b.tablespace_name = a.tablespace_name (+) and
c.file# (+)= a.file_id and
b.file_id = a.file_id (+)
 group
by b.file_name,
decode(c.inc,null,’no’,’yes’),
b.tablespace_name
 order
by 5 desc, 1;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: