Oracle Database - Table Size
Table of Contents
About
How to calculate the space of a table ?
Articles Related
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'
);