Sunday, August 16, 2015

Oracle : Script which shows who owns which objects and where

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
/

No comments:

Post a Comment