Index/table fragmentation scripts

-- identify all tables with more than 20% of rows deleted

select
T.TABLE_NAME,
t.partition_name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,'999999.99') per_del,
ROUND(((SUM(T.UPDATES)+SUM(T.INSERTS)+SUM(T.DELETES))/(DECODE(a.NUM_ROWS,0,1,a.NUM_ROWS))*100.0),2) PER_CHG
from ALL_TAB_MODIFICATIONS T,
all_TABLES a
where T.timestamp >= TO_DATE('01-JAN-2001','dd-mon-yyyy')
and T.TABLE_NAME=a.TABLE_NAME
having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100 >=20
group by
T.TABLE_NAME, t.partition_name, a.NUM_ROWS
order by NUM_ROWS desc, t.table_name;

-- get fragmentation factor

select OWNER,TABLE_NAME, LAST_ANALYZED, NUM_ROWS,AVG_ROW_LEN,ROUND(blocks*8/1024) MB, ROUND(blocks*7297/AVG_ROW_LEN/NUM_ROWS,1)"factor" from DBA_TABLES
  where blocks>1000 and num_rows>0 and owner not like 'SYS%' order by 1,7;

-- get BLEVEL for indexes (BLEVEL>=4 must be rebuild)

select t.owner,t.table_name,round(t.blocks*8/1024) "tMB", i.index_name, round(i.leaf_blocks*8/1024) "iMB", i.blevel from DBA_INDEXES i, DBA_TABLES t where t.table_name=i.table_name AND t.blocks<i.leaf_blocks*3 and t.blocks>6400 and t.owner not like 'SYS%' order by 1,3;