Script to extract total objects & size of all Schemas

Script to extract total objects & size of all Schema’s:

By issuing below script you will find out all schema’s along with size they have yet consumed, script will find out all users. ( system users + user defined users)
Also you can find out total number of objects contains that schema.


Issue following query as sys user:

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) segment
where obj.owner = segment.owner(+)
order by 3 desc, 2 desc, 1;

Owner                          Objects     size MB
——————————     ———-      ———-
SYS                            30965       990
XDB                            842          128
APEX_030200             2406         78
SYSMAN                     3491         46

. .. …

Note: Please don’t hesitate to revert in case of any query OR feedback.
Thanking you.
Have a easy life ahead.

Leave a Reply