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. 🙂