Objects

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

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


Owner                               Objects      size MB
------------------------------ ------------ ------------
OMDSS                                43,855    2,105,198
OMEDW                                19,627    1,159,418
OMSTG                                 7,815      776,717
OMDSS_PRF                            27,567      575,393
OMEDW_PRF                             6,507      480,971
OMSTG_PRF                             4,104      351,780
OBIEDW                                5,209      314,094
SYS                                  14,317      187,567
OBIEDW_PRF                            4,515      177,136
PMFODS                                  912       44,965
PMFODS_PRF                              882       30,982

SELECT owner,
segment_name,
table_name,
--partition_name,
--segment_type,
sum (bytes / 1024/1024) "size_in_mb"
FROM dba_segments
WHERE segment_type IN ('INDEX', 'INDEX PARTITION')
and owner='OMDSS_PRF'
group by  owner,segment_name,table_name