largest segment (table or index) in each Tablespace

column tablespace format a18
column segment format a23
select a.ts tablespace,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.tsb),’9,999,999,999,999′) ” TS BYTES”,
to_char(sum(a.segb)/decode(count(*),1,1,count(*)-1)/decode(sum(a.tsb),0,1,
sum(a.tsb))*100,’990.99′)
” PCT”
from (
select tablespace_name ts,segment_name seg,
sum(bytes) segb,000000000000 tsb
from dba_segments d
where tablespace_name not in (‘SYSTEM’,’RBS’,’TEMP’,’TOOLS’,’TSMSYS’,’DBSNMP’,’OUTLN’,’UNDOTBS’)
group by tablespace_name,segment_name
having sum(bytes) = (
select max(sum(bytes))
from dba_segments c
where c.tablespace_name = d.tablespace_name
group by c.segment_name)
union all
select tablespace_name,’~’,0,sum(bytes)
from dba_segments
where tablespace_name not in (‘SYSTEM’,’RBS’,’TEMP’,’TOOLS’,’TSMSYS’,’DBSNMP’,’OUTLN’,’UNDOTBS’)
group by tablespace_name) a
group by a.ts;
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: