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,’ ‘,’ +’)
to_char(sum(a.owb),’9,999,999,999,999′) ” OWNER BYTES”,
” 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


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: