← Space Management
Oracle · Space Management

How to check the size of a table in Oracle (table, indexes, LOBs)

Check the total size of an Oracle table including its indexes and LOB segments.

Tested on11g12c19c21c23ai26ai

The total footprint of one table: its own segment, its indexes, and its LOB segments, broken down by type. Prompts for owner and table name.

SELECT segment_type,
       COUNT(*)                       AS segments,
       ROUND(SUM(bytes)/1024/1024, 1) AS mb
FROM   dba_segments
WHERE  owner = UPPER('&&owner')
AND    (
         segment_name = UPPER('&&table_name')
      OR segment_name IN (SELECT index_name FROM dba_indexes
                          WHERE table_owner = UPPER('&owner')
                          AND   table_name  = UPPER('&table_name'))
      OR segment_name IN (SELECT segment_name FROM dba_lobs
                          WHERE owner = UPPER('&owner')
                          AND   table_name = UPPER('&table_name'))
       )
GROUP  BY segment_type
ORDER  BY mb DESC;
spacetable-sizedba_segments