Oracle – all_tables vs dba_segments Space Discrepancy

oracle

When I ran the below queries they gave different space occupied by the same table:

select bytes/1024/1024 as space, owner
from dba_segments
where segment_name='PARAMETER_STATUS';

     SPACE OWNER
---------- ------------------------------
      4988 FLSTD01
      3124 FLSTD04
      6416 FLSTD02
       808 FLSTD03

select blocks*16/1024,owner
from all_tables
where table_name='PARAMETER_STATUS';

BLOCKS*16/1024 OWNER
-------------- ------------------------------
    4979.39063 FLSTD01
      802.1875 FLSTD03
    6405.98438 FLSTD02
    1966.34375 FLSTD04

Why is it giving me different space occupied by the same table?

DB_BLOCK_SIZE=16KB

Best Answer

According to Oracle documentation, dba_segments.bytes is the number of bytes used by the database segment. all_tables.blocks is the number of blocks used inside a table (docs). Tables don't grow block by block, they grow extent by extent. There is also a third interesting field, dba_segments.blocks, which shows how many database blocks are there in a segment.

You can run the following query and see that the number of used blocks in table is generally not the same as the number of blocks in corresponding segments:

select s.segment_name, s.blocks segment_blocks, t.blocks table_used_blocks
from dba_segments s
    inner join dba_tables t on s.owner = t.owner and s.segment_name = t.table_name
where t.table_name = 'PARAMETER_STATUS'

Also note that due to partitioning, a single table may consist of more than one segment.

Related Question