One of the most problem when monitoring team or dba wants to check tablespace usage is real space.
In fact in the most query the query shows free space base on amount of extended space and somebody has problem because you have to calculate that how much space is remaining on your tablespace of whole. I changed a script for getting to this purpose and also it supports bigfile. you can use it below query o check your real free space of your tablespaces.
--if bigfile=no the each datafile=32 GB
--if bigfile=YES the each datafile=32768 GB
--and also db_block_size=8192
SELECT *
FROM (SELECT SUBSTR(c.contents, 1, 1) C,
a.tablespace_name,
a.alloc_gb,
a.alloc_gb - nvl(b.free_gb, 0) used_gb,
nvl(b.free_gb, 0) free_gb,
a.file_count,
CASE
WHEN (a.max_gb - a.alloc_gb) <= 0 THEN
ROUND((a.alloc_gb - nvl(b.free_gb, 0)) / a.alloc_gb * 100)
ELSE
ROUND((a.alloc_gb - nvl(b.free_gb, 0)) / a.max_gb * 100)
END pct_used,
case
when (c.bigfile = 'NO') then
(32 * a.file_count) - (a.alloc_gb - nvl(b.free_gb, 0))
when (c.bigfile = 'YES') then
(32768 * a.file_count) - (a.alloc_gb - nvl(b.free_gb, 0))
end real_free_SIZE_GB
FROM (SELECT tablespace_name,
COUNT(*) file_count,
ROUND(SUM(bytes) / 1024 / 1024 / 1024) alloc_gb,
ROUND(SUM(DECODE(maxbytes, 0, bytes, maxbytes) / 1024 / 1024 / 1024)) max_gb
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1024 / 1024 / 1024) free_gb
FROM dba_free_space
GROUP BY tablespace_name) b,
dba_tablespaces c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
UNION
SELECT SUBSTR(c.contents, 1, 1) C,
a.tablespace_name,
a.alloc_gb,
NVL(b.used_gb, 0) used_gb,
a.alloc_gb - NVL(b.used_gb, 0) free_gb,
a.file_count,
CASE
WHEN (a.max_gb - a.alloc_gb) <= 0 THEN
ROUND(NVL(b.used_gb, 0) / a.alloc_gb * 100)
ELSE
ROUND(NVL(b.used_gb, 0) / a.max_gb * 100)
END
pct_used,
case
when (c.bigfile = 'NO') then
(32 * a.file_count) - (used_gb)
when (c.bigfile = 'YES') then
(32768 * a.file_count) - (used_gb)
end real_free_SIZE_GB
FROM (SELECT tablespace_name,
COUNT(*) file_count,
ROUND(SUM(bytes) / 1024 / 1024 / 1024) alloc_gb,
ROUND(SUM(DECODE(maxbytes, 0, bytes, maxbytes) / 1024 / 1024 / 1024)) max_gb
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace tablespace_name,
ROUND(SUM(blocks * b.VALUE) / 1024 / 1024 / 1024, 2) used_gb
FROM v$tempseg_usage a, v$parameter b
WHERE b.name = 'db_block_size'
GROUP BY tablespace) b,
dba_tablespaces c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name)
ORDER BY 7 DESC NULLS LAST, 2
