Query to find space usage of db objects

Query to find space usage of db objects

Below query displays the space consumed by every object user owns in MB.

SELECT segment_name, sum(bytes)/1048576 Megs
FROM user_extents
WHERE segment_name like '%'
GROUP BY segment_name
/

If the table/index is partitioned, the the space consumed by each partition can be queried using below query.

SELECT segment_name, partition_name, sum(bytes)/1048576 Megs
FROM user_extents
where segment_name='TABLE_NAME'
GROUP BY segment_name, partition_name
order by 3
/