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)

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Pardy DBA

ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs

HeliFromFinland

Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

The Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet.com

Stuff that interests me, if not you!

Uwe Hesse

about Database Technology

%d bloggers like this: