管理脚本系列（1）-查找索引较大的表

1. 今天看到生产库里有个索引是表的2倍大，写了个脚本查一下到底有多少size比较夸张的索引：

select t.tab_name,t.tab_size,i.ind_name,i.ind_size,round(i.ind_size/t.tab_size,2)*100||'%' as size_pct
from
(select segment_name tab_name,segment_type,sum(round(bytes/1024/1024)) tab_size
from
dba_segments
where
owner='SCOTT' and segment_type like 'TABLE%'
group by segment_name,segment_type) t,
(select segment_name ind_name,segment_type,sum(round(bytes/1024/1024)) ind_size
from
dba_segments
where wner='SCOTT' and segment_type like 'INDEX%'
group by segment_name,segment_type) i,
dba_indexes d
where d.index_name=i.ind_name
and
d.table_name=t.tab_name
and
t.tab_size>0
and
i.ind_size>0
and
i.ind_size/t.tab_size>0.2;