How-to: Find The Size of All Tables In An Oracle Database

I came across the need to total up the size of all tables in an Oracle database. The server housing the current database was End of Life and we needed to spec out the size of its replacement.

I did not have DBA level access to the database but I did have the database user and password used by the application. Knowing that 90% + of your data will be in the user tables and the DBA tables will not be that large, I was able to come up with a query to total the User Tables. Then I made a guesstimate on how large a default database is, added the two together and I had a good enough number for my capacity planning.

So here are the queries to total all the tables in an oracle database as the database user.

Finding the Size of all Tables in an Oracle Database

In Mega Bytes:

select segment_name,sum(bytes)/1024/1024 as "SIZE in MB" from user_segments where segment_type='TABLE' group by segment_name order by "SIZE in MB" desc;

In GigaBytes:

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_type='TABLE' group by segment_name order by "SIZE in GB" desc;