← 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