Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 6 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:

select
  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
add comment

Your Answer

 
discard

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.