Monday, March 9, 2009

Extents: Tables witha big number of extents

select dbsname, tabname table_or_index, count(*) num_of_extents,
trunc(sum( pe_size * $PAGE_SIZE ) /1024/1024,0) total_size_MB
from systabnames, sysptnext
where systabnames.partnum = pe_partnum
and dbsname != 'HASHTEMP'
and systabnames.tabname not matches 'TBL*'
group by 1,2
having count(*) > $NUM_EXTENTS
order by 1,3 desc,2
-- PAGE_SIZE in bytes, please

No comments: