Distribution of objects and data

Distribution of objects and data

Which schemas are taking up all of the space

set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from  (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
 from dba_segments group by owner) seg
where  obj.owner  = seg.owner(+)
order by 3 desc ,2 desc, 1
/

Output:

Owner                               Objects      size MB
------------------------------ ------------ ------------
ANDY                                    219       46,323
SYS                                  22,923          706
SYSMAN                                1,341           49
XDB                                     680           49
MDSYS                                   896           33
SYSTEM                                  454           23
OLAPSYS                                 720           16
WMSYS                                   242            7
CTXSYS                                  339            5
EXFSYS                                  281            4
DBSNMP                                   46            2
ORDSYS                                1,720            1
DMSYS                                   189            1
OUTLN                                     8            1
SCOTT                                     6            1
TSMSYS                                    3            1
PUBLIC                               19,990            0
ORDPLUGINS                               10            0

18 rows selected.

SQL>