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)

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

w

Connecting to %s

%d bloggers like this: