How to calculate the space of a table ?
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:
Therefore, our table
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'
);