query to find size of schema in oracle


Execute this query to find the size of each schema in oracle database,

set pages 100
select    a.owner "Owner",  cnt_obj "No. of Objects", decode(seg_mb, NULL, 0, seg_mb) "Size in MB"
from   
     (select owner, count(*) cnt_obj from dba_objects group by owner) a ,
     (select owner, ceil(sum(bytes)/1024/1024) seg_mb  from dba_segments group by owner) b
where     a.owner  = b.owner(+)
order    by 3 desc ,2 desc, 1;


Thank you,

1 comment: