Find the largest segment (table or index) for each owner

column owner format a18 trunc
column segment format a23 trunc
set line 5000
select a.ow owner,min(a.seg) || decode(count(*),1,'(none)’,2,’ ‘,’ +’)
segment,
to_char(sum(a.segb)/decode(count(*),1,1,count(*)-1),’9,999,999,999′)
” SEG BYTES”,
to_char(sum(a.owb),’9,999,999,999,999′) ” OWNER BYTES”,
to_char(sum(a.segb)/decode(count(*),1,1,count(*)-1)/decode(sum(a.owb),0,1,
sum(a.owb))*100,’990.99′)
” PCT”
from (
select owner ow,segment_name seg,
sum(bytes) segb,000000000000 owb
from dba_segments d
where owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’TSMSYS’,’DBSNMP’,’OUTLN’)
group by owner,segment_name
having sum(bytes) = (
select max(sum(bytes))
from dba_segments c
where c.owner = d.owner
group by c.segment_name)
union all
select owner,’~’,0,sum(bytes)
from dba_segments
where owner not in (‘SYS’,’SYSTEM’,’SCOTT’,’TSMSYS’,’DBSNMP’,’OUTLN’)
group by owner) a
group by a.ow;
set linesize 80 termout on heading on pagesize 24 timing on feedback 6
set termout on verify on echo on showmode both

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: