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.


1 Answer 1


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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