Space used by objects

Cyrille Modiano
Latest posts by Cyrille Modiano (see all)

Calculate the space used by a single object

This script will help you calculate the size of a single object :

SELECT  s.owner, 
        s.segment_name, 
        s.bytes/1024/1024 "Size(MB)"
FROM    dba_segments s,
        dba_lobs l
WHERE   s.owner = '&schema_name'
  AND   s.owner=l.owner(+)
  AND   s.segment_name = 
        CASE WHEN s.segment_type = 'LOBSEGMENT' 
        THEN L.SEGMENT_NAME(+)
        WHEN s.segment_type = 'LOBINDEX'
        THEN L.INDEX_NAME(+)
        END
AND     s.SEGMENT_NAME='&segment_name';

Calculate the space used by a whole schema

If you want the space used by a whole schema, then here is a variation of the first query :

SELECT  s.owner, 
        SUM(s.bytes/1024/1024) "Size(MB)"
FROM    dba_segments s,
        dba_lobs l
WHERE   s.owner = '&schema_name'
  AND   s.owner=l.owner(+)
  AND   s.segment_name = 
        CASE WHEN s.segment_type = 'LOBSEGMENT' 
        THEN L.SEGMENT_NAME(+)
        WHEN s.segment_type = 'LOBINDEX'
        THEN L.INDEX_NAME(+)
        END
group by s.owner
order by 2;

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.