Blog name change notification

Dear Readers,

I'll be switching to orabliss.blogspot.com from next week. As of now the blog contains nothing but the notification message alone.
Please make a note of it and follow me there for updates. An automatic redirection for all the posts will be provided from this blog's content even if you land on this blog (oraclemamukutti.blogspot.com) for an initial few days and I will discontinue this blog. 
Please provide your support in my new blog as well.

Thanks!

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.

SQL> 
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') 
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.