Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to estimate the table sizes within my schema (in MB). This is what I have so far:

SELECT table_name, owner, last_analyzed
FROM all_tables

I'm fairly new to SQL so I have no idea how I would go around doing this.

Thank you.

share|improve this question
Thanks a lot, Benoit, You Rock!!! – user5910 Jan 19 '12 at 2:06
up vote 9 down vote accepted

Look at the "dba_segments" view (or user_segments if you don't have dba rights). The following query should give you what you're looking for:

  owner as "Schema"
  , segment_name as "Object Name"
  , segment_type as "Object Type"
  , round(bytes/1024/1024,2) as "Object Size (Mb)"
  , tablespace_name as "Tablespace"
from dba_segments
order by owner;
share|improve this answer
Hi Benoit, it worked perfectly thank you. I took a look at the table fields that I could be using, but I wasn't able to find "Owner" or last updated fields. By the way, I used the user_segments view. Would this information be available under dba_segments? – Diego R Jun 23 '11 at 16:46
All segments shown in user_segments are owned by the account you used to log you on, that's why there's no owner column. – Benoit Jun 24 '11 at 7:35

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.