Oracle PL/SQL: How Big Are My Tables?

As part of a change request I just happened to need to get a "ball-park" figure on the sizes of some of the key tables in a schema. For that reason I put together the following PL/SQL;

SELECT NVL(di.table_name, DS.segment_name) "Table Name",
       ROUND((sum(ds.bytes) / 1048576 /* 1 MB in bytes */), 3) "MB"
  FROM dba_segments DS
  LEFT JOIN dba_indexes DI
    ON DI.owner = DS.owner
   AND DI.index_name = DS.segment_name
 WHERE 1 = 1
   AND DS.segment_type IN ('INDEX', 'TABLE')
   AND DS.owner = USER -- Currently logged in user
 GROUP BY NVL(di.table_name, DS.segment_name)
 ORDER BY sum(ds.bytes) DESC

It works by looking at the sizes of both the table itself and any assocaited indexes. It's worth noting that there are other components (notably LOB's) which can have a drastic effect on table size which I'm not checking for here (primarily because I'm not interested in them - we typically put those into another table space that is monitored separately).

In some cases you might want the break down based on the segment_type (so you can tell the difference between lots of table data and lots of indexes) but I think by studying the above you get the gist of what would need to be tweaked to do that.

Hopefully this will prove useful to those of you wanting quick estimates to provide to support.