Sizing an Oracle schema from SQL*Plus


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
(select t1.owner
,t1.object_name, t1.object_type
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)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: