Looking for something? Try here..

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.
 
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.

2 comments:

  1. this script shows no rows selected

    ReplyDelete
    Replies
    1. I believe you are using the code without replacing the 'OWNER' (line 26) with the user you require the information for. Please replace it by username you are trying to calculate & run again.
      I'm updating the code to highlight this.
      Thanks!

      Delete