Page 1 of 1

Query to find total objects & size of all Schemas

Posted: Wed Mar 05, 2014 5:00 pm
by jethwa.jignesh
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
. .. ...

Source:
http://dbatricksworld.com/?p=98

Re: Query to find total objects & size of all Schemas

Posted: Thu Feb 26, 2015 7:20 pm
by markjacks
This one doesn't work with the latest version anymore though.