Oracle Database - Table Size

1 - About

How to calculate the space of a table ?

3 - How to

3.1 - Not Partitioned

SELECT
  extent_id,
  bytes,
  blocks
FROM
  user_extents
WHERE
  segment_name   = 'YourTableNotPartioned'
AND segment_type = 'TABLE';

Then:

SELECT
  blocks,
  empty_blocks,
  avg_space,
  num_freelist_blocks
FROM
  user_tables
WHERE
  TABLE_NAME = 'YourTable';
    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
         1           53       6091                   1

The above shows us:

  • we have 55 blocks allocated to the table
  • 53 blocks are totally empty (above the HWM)
  • 1 block contains data (the other block is used by the system)
  • we have an average of about 6k free on each block used.

Therefore, our table

  • consumes 1 block
  • of which 1 block * 8k blocksize - 1 block * 6k free = 2k is used for our data.

That you can also calculate so:

SELECT
  TABLE_NAME,
  (blocks - num_freelist_blocks) * blocksize / 1024 AS "Size (k)"
FROM
  (SELECT
    TABLE_NAME,
    blocks,
    (SELECT
      VALUE
    FROM
      v$parameter
    WHERE
      name = LOWER('DB_BLOCK_SIZE')
    ) blocksize,
    empty_blocks,
    avg_space,
    num_freelist_blocks
  FROM
    user_tables
  WHERE
    TABLE_NAME = 'FACT_KPIS'
  );

4 - Documentation / Reference

db/oracle/table_size.txt ยท Last modified: 2017/09/06 19:29 by gerardnico