Sizing an Oracle schema from SQL*Plus

Problem:

Identify the correct size of a schema in the Oracle10g Database.

Solution;

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.

Script:

select round(sum(object_size)/1024/1024,0) schema_size
from
(select t1.owner
,t1.object_name, t1.object_type
,t2.object_size
from dba_objects t1,
(select segment_type, segment_name, sum(bytes) object_size
from dba_extents
group by segment_type, segment_name) t2
where t1.object_name = t2.segment_name (+)
and t1.owner = upper(‘&1’)
order by t1.object_name)

Please follow and like: