Fragmentation Script

To Calculate Fragmentation for Schema

select a.owner, a.TABLE_NAME, b.SIZE_GB, ((a.BLOCKS*8192/1024/1024/1024)-(a.NUM_ROWS*AVG_ROW_LEN/1024/1024/1024)) as ACTUAL_GB,
(b.SIZE_GB-((a.BLOCKS*8192/1024/1024/1024)-(a.NUM_ROWS*AVG_ROW_LEN/1024/1024/1024))) Savings,
a.TABLESPACE_NAME, b.SEGMENT_TYPE TABLE_TYPE, a.last_analyzed
from
dba_tables a,
(select * from
 (select owner, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, sum(bytes/1024/1024/1024) Size_GB
 from
 dba_segments
 where owner='&schema_name' and segment_type like '%TABLE%'
 group by segment_name, owner, SEGMENT_TYPE, TABLESPACE_NAME
 order by 5 desc) ) b
where a.TABLE_NAME=b.SEGMENT_NAME and a.owner=b.owner
order by SIZE_GB desc;


Index Fragmentation:

select e.*, d.owner index_owner,d.index_name, d.index_type, sum(g.bytes/1024/1024/1024) index_size_gb, d.TABLESPACE_NAME index_tbs from dba_segments g, dba_indexes d
right outer join
 (select * from (
select a.owner tabown, a.TABLE_NAME, b.SIZE_GB, ((a.BLOCKS*8192/1024/1024/1024)-(a.NUM_ROWS*AVG_ROW_LEN/1024/1024/1024)) as ACTUAL_GB,
(b.SIZE_GB-((a.BLOCKS*8192/1024/1024/1024)-(a.NUM_ROWS*AVG_ROW_LEN/1024/1024/1024))) Savings_GB,
a.TABLESPACE_NAME tab_tbs, b.SEGMENT_TYPE TABLE_TYPE, a.last_analyzed
from
dba_tables a,
(select * from
 (select owner tabown, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, sum(bytes/1024/1024/1024) Size_GB
 from
 dba_segments
 where owner='&schema_name' and segment_type like '%TABLE%'
 group by segment_name, owner, SEGMENT_TYPE, TABLESPACE_NAME
 order by 5 desc)
where rownum<11 b="" br="">where a.TABLE_NAME=b.SEGMENT_NAME and a.owner=b.tabown
order by SIZE_GB desc))
e on e.tabown=d.table_owner and e.table_name=d.table_name
where g.segment_name=d.index_name and d.owner=g.owner
group by
e.tabown, e.table_name, size_gb, actual_gb, e.savings_GB, e.tab_tbs, e.table_type, e.last_analyzed, d.owner, d.index_name, d.index_type, d.tablespace_name
order by size_gb desc
;