Wednesday, January 7, 2015

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.

No comments:

Post a Comment

I don't know what you think about my creation, but every words of you help me grow better and stronger!!