Skip to Main Content
  • Questions
  • Is there a way to find occupied sizes of all tables(of all columns of each table) in oracle db ?


Question and Answer

Connor McDonald

Thanks for the question, Tirumala.

Asked: November 19, 2020 - 8:05 pm UTC

Last updated: November 24, 2020 - 1:45 am UTC

Version: 11.2

Viewed 100+ times

You Asked

I need sizes of all columns of a table and the same details of all user tables in db.

-- Query to find # of rows and sizes of All data in Table1
select count(1),
from TABLE1;

Should I construct similar query for all tables and get the info OR is there a way to do automatically pull all tables sizes ?

and we said...

You could do that (I'd recommend using VSIZE instead of LENGTH where appropriate).

However that does seem like huge overkill to me, when BLOCKS from USER_TABLES will typically be "good enough" unless you've been doing huge maintenance activities.

Also, check out this post for a utility that might be use

More to Explore


Need more information on Administration? Check out the Administrators guide for the Oracle Database