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.