Identify the correct size of a schema in the Oracle10g Database.
This is a relative simple query to find out what is the size of the schema in megs. This script uses the dba_objects table and the dba_extents table to find the size of every object for the schema and then sum the total bytes of each object into one rounded number.
select round(sum(object_size)/1024/1024,0) schema_size
from dba_objects t1,
(select segment_type, segment_name, sum(bytes) object_size
group by segment_type, segment_name) t2
where t1.object_name = t2.segment_name (+)
and t1.owner = upper(‘&1’)
order by t1.object_name)