11

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.

10

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;
| 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
  • Correct prefix for 1024/1024 is Mi rather than M (see Binary prefix) – Wernfried Domscheit Apr 8 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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