Oracle sort tables by size

When a tablespace is filling up and you need to know what’s eating the space, this query is the first thing I run. It queries user_segments — which includes not just the table data but also indexes — and sorts by size descending. Usually one or two tables account for the majority of space.

• Query is:

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