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 ?

Breadcrumb

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),
sum(length(column1)),
sum(length(column2)),
sum(length(column3)),
sum(length(column4)),
sum(length(column5))
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

https://asktom.oracle.com/pls/apex/asktom.search?tag=show-space-procedure-usage-problem


More to Explore

Administration

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