Fragmentation

Script to Find out Fragmentation

 
select substr(de.owner,1,8) "Owner",
substr(de.segment_type,1,8) "Seg Type",
substr(de.segment_name,1,20) "Table Name (Segment)",
substr(de.tablespace_name,1,20) "Tablespace Name",
count(*) "Frag NEED"
from dba_extents de, v$datafile df
where de.owner <> 'SYS'
and de.file_id = df.file#
and de.segment_type in ('TABLE','INDEX')
group by de.owner, de.segment_name, de.segment_type, de.tablespace_name,
df.name
having count(*) > 40
order by count(*) desc;