Skip to content

Oracle query: table size in bytes

SELECT TRUNC(sum(bytes)) table_size, table_name 
FROM (SELECT segment_name table_name, bytes
    FROM user_segments
    WHERE segment_type = 'TABLE'
    UNION ALL
    SELECT i.table_name, s.bytes
    FROM user_indexes i, user_segments s
    WHERE s.segment_name = i.index_name 
    AND   s.segment_type = 'INDEX'
    UNION ALL
    SELECT l.table_name, s.bytes
    FROM user_lobs l, user_segments s
    WHERE s.segment_name = l.segment_name 
    AND   s.segment_type = 'LOBSEGMENT'
    UNION ALL
    SELECT l.table_name, s.bytes
    FROM user_lobs l, user_segments s
    WHERE s.segment_name = l.index_name 
    AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name
HAVING SUM(bytes)/1024/1024 > 100