Tip of the day - Table and index size in Oracle

Ever wanted to find out how big are the tables in your data warehouse or in your ETL storage area? Here is a quick tip.

You can get the size of each table belonging to a specific user with the following code:

select sum(bytes)/1048576 Size_MB, segment_name Table_name
from user_extents
where segment_name in (
     select table_name from all_tables
     where owner = 'OWNER_NAME_HERE')
group by segment_name
order by 1 desc;

In order to get the size of the indexes with the corresponding table names (useful when the indexes have system-generated names), we need another query:

select sum(u.bytes)/1048576 Size_MB, u.segment_name index_name, i.table_name
from user_extents u
join all_ind_columns i
     on u.segment_name = i.index_name
     and i.column_position = 1
where i.index_owner = 'OWNER_NAME_HERE'
group by u.segment_name, i.table_name
order by 1 desc;
If you have any doubts or suggestions, leave a comment below.