Oracle Schemea Objects are taking up most space

How much space does the Oracle Schema take in Database?  How many objects are in each Oracle Schema?

Show Schema Object per Database and total size in megabytes

set lines 200
set pages 999
col "Owner" format a15
col "MB's" format 999,999,999
col "Obj" format 999,999,999
select    obj.owner "Owner"
,    obj_cnt "Objects"
,    decode(seg_size, NULL, 0, seg_size) "MB's"
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
/

Show Schema Object per Database and total size in megabytes

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

Leave a Reply

Your email address will not be published. Required fields are marked *