Calculating space used by a table in Oracle – all tablespaces

The following query will get the space (on disk) being utilized by a table. This would include space for that table in all tablespaces that the table data is persisted to.


SELECT sum( bytes)/1024/1024 size_in_MB
FROM user_segments
WHERE (segment_name = :1
OR segment_name in (
SELECT segment_name
FROM user_lobs
WHERE table_name = :1
UNION
SELECT index_name
FROM user_lobs
WHERE table_name = :1
) );

Here: “:1″ => name of table.

Note that a table maps to segments.

Individual db objects such as tables, indexes are stored in segments that are collections of extents which in turn are comprised of contiguous data blocks. If an extent runs out of space then another extent (a datablock in that extent) is made available and that may not be contiguous.

A table can have many segments associated with it – one could be for the table, another for one of the indexes defined on the table and so on. Partitioned tables have system generated indexes as well.

A data block size is usually 8192 bytes.

To get size of a table broken down according to the tablespaces, a different set of queries are used. This could happen if table columns are persisted into different tablespaces. For instance: column 1 in tablespace 1 and so on.

To get those nos, you would have to repeat the following for the table, each index in that table and so on:

select sum(bytes) from user_segments where segment_name = ‘$1′;

Replace ‘$1′ with name of table or the name of the indexes defined on it.