Script to find out total number of oracle schema objects and its size

Following script help you to find out available schema’s along with total number of objects and its size ( in MB )

SQL> set pages 999
SQL> col "size MB" format 999,999,999
SQL> col "Objects" format 999,999,999
SQL> 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;

O/P:

Owner                          Objects      size MB
------------------------------ ------------ ------------
PRODAPP                        32,527       39,433
SYS                            30,924       5,521
SYSTEM                         604          4,468
AEX_030200                     2,406        157
XPRDB                          844          129
SYSMAN                         3,491        123
GHSYS                          1,019        29
TRXSYS                         366          19
...

Stay Tune. 🙂

Leave a Reply