Total used space of a table

The total used space of a table should be calculated by using the summation of sizes of the table's segment, it's indexes segment and the segment sizes of the lob and lobindexes associated with it. This can be achieved by the below script which calculates the size of all the tables that belongs to a particular schema and lists from the biggest to the smallest.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

select * from ( SELECT owner, table_name, sum(bytes)/1024/1024 MB FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION') UNION ALL SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION') UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner = l.owner AND s.segment_type = 'LOBSEGMENT' UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = 'LOBINDEX') WHERE owner in UPPER('OWNER') -- replace with your desired USERNAME -- orabliss.blogspot.com GROUP BY table_name, owner order by mb desc) ;

Update: 22-Jan-2015
Query handles partitioned tables and IOT as well. IOT segments are listed as separate tables. It should be mapped accordingly to the IOT tables.

Source: Various sites and Forums.