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;

再发一个：

col owner for a10
col table_name for a25
col IND2TAB_PER for a15
col index_name for a15
select i.owner,i.table_name,i.index_name,t.bytes tab_size_MB, ii.bytes ind_size_MB,
round(ii.bytes/decode(t.bytes,0,1,t.bytes),2)*100||'%' ind2tab_per
from dba_indexes i,
  (select g.owner,g.segment_name,round(sum(bytes)/1024/1024) bytes
  from dba_segments g
  group by g.owner,g.segment_name) t,
  (select g.owner,g.segment_name,round(sum(bytes)/1024/1024) bytes ,sum(EXTENTS) EXTENTS
  from dba_segments g
  group by g.owner,g.segment_name) ii
where t.bytes  < ii.bytes * 1.5
  and t.segment_name = i.table_name
  and t.owner = i.table_owner
  and ii.segment_name = i.index_name
  and ii.owner = i.owner
  and ii.bytes > 100 --index had been extended
  and i.INITIAL_EXTENT *1.2  < ii.bytes*1024*1024 -- more than 1 extentes
order by 5 desc;