Query to find total objects & size of all Schemas

Post Reply
User avatar
jethwa.jignesh
Posts: 17
Joined: Tue Mar 04, 2014 12:30 pm
Location: Nasik
Contact:

Query to find total objects & size of all Schemas

Post by jethwa.jignesh » Wed Mar 05, 2014 5:00 pm

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
Thanks & Regards,
Jignesh Jethwa
UkHostGator Moderator
http://dbatricksworld.com

User avatar
markjacks
Posts: 82
Joined: Sun Nov 30, 2014 11:56 pm

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

Post by markjacks » Thu Feb 26, 2015 7:20 pm

This one doesn't work with the latest version anymore though.
Data back up is a must. Image

Post Reply