Oracle Database - Table Size

Card Puncher Data Processing

About

How to calculate the space of a table ?

How to

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'
  );

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - Table

A table name must follow the naming convention of database object. Add DataType Create Table ...



Share this page:
Follow us:
Task Runner