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 1000+ 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 Connor 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



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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