select
substrb(decode(grouping(owner),1,'{all}',owner),1,10) OWNER,
substrb(decode(grouping(tablespace_name),1,'{all}',tablespace_name),1,13) TS_NAME,
substrb(decode(floor(sum(bytes)/1073741824),0,
decode(floor(sum(bytes)/1048576),0,
to_char(sum(bytes)/1024,'9999')||'K',
to_char(sum(bytes)/1048576,'9999')||'M'),
to_char(sum(bytes)/1073741824,'99.9')||'G'),2,6) "SIZE",
to_char(sum(decode(segment_type,'TABLE',1,0)),'999999') TAB,
to_char(sum(decode(segment_type,'INDEX',1,0)),'999999') IDX,
substrb(to_char(sum(decode(segment_type,'ROLLBACK',1,0)),'999'),2,3) ROL,
substrb(to_char(sum(decode(segment_type,'TEMPORARY',1,0)),'999'),2,3) TMP,
substrb(to_char(sum(decode(segment_type,'CACHE',1,0)),'999'),2,3) CAC,
substrb(to_char(sum(decode(segment_type,'CLUSTER',1,0)),'999'),2,3) CLU,
substrb(to_char(sum(decode(substrb(segment_type,1,3),'LOB',1,0)),'999'),2,3) LOB,
to_char(count(*),'9999999') TOTAL
from dba_segments
group by rollup( owner, tablespace_name )
order by 1,2,3 desc,4 desc
/
substrb(decode(grouping(owner),1,'{all}',owner),1,10) OWNER,
substrb(decode(grouping(tablespace_name),1,'{all}',tablespace_name),1,13) TS_NAME,
substrb(decode(floor(sum(bytes)/1073741824),0,
decode(floor(sum(bytes)/1048576),0,
to_char(sum(bytes)/1024,'9999')||'K',
to_char(sum(bytes)/1048576,'9999')||'M'),
to_char(sum(bytes)/1073741824,'99.9')||'G'),2,6) "SIZE",
to_char(sum(decode(segment_type,'TABLE',1,0)),'999999') TAB,
to_char(sum(decode(segment_type,'INDEX',1,0)),'999999') IDX,
substrb(to_char(sum(decode(segment_type,'ROLLBACK',1,0)),'999'),2,3) ROL,
substrb(to_char(sum(decode(segment_type,'TEMPORARY',1,0)),'999'),2,3) TMP,
substrb(to_char(sum(decode(segment_type,'CACHE',1,0)),'999'),2,3) CAC,
substrb(to_char(sum(decode(segment_type,'CLUSTER',1,0)),'999'),2,3) CLU,
substrb(to_char(sum(decode(substrb(segment_type,1,3),'LOB',1,0)),'999'),2,3) LOB,
to_char(count(*),'9999999') TOTAL
from dba_segments
group by rollup( owner, tablespace_name )
order by 1,2,3 desc,4 desc
/
No comments:
Post a Comment