Query on DBA_TABLESPACE_USAGE_METRICS returns correct data, whereas combination of DBA_FREE_SPACE,DBA_DATA_FILES returning wrong result:
SELECT
a.tablespace_name,
ROUND((a.tablespace_size * b.block_size) / (1024*1024), 2) AS "Tablespace size (MB)",
ROUND((a.used_space * b.block_size) / (1024*1024), 2) AS "Used space (MB)",
ROUND((a.tablespace_size * b.block_size) / (1024*1024), 2) - ROUND((a.used_space * b.block_size) / (1024*1024), 2) "Free space (MB)",
ROUND(a.used_percent, 2) AS "Used %"
FROM dba_tablespace_usage_metrics a
JOIN dba_tablespaces b ON a.tablespace_name = b.tablespace_name
WHERE a.tablespace_name ='APP_DATA_TABLESPACE'
returns correct value as
tablespace_name= 'APP_DATA_TABLESPACE',
Tablespace size (MB)=126976
Used space (MB) =4
Free space (MB)=126972
Used %=0
whereas
select t.tablespace,
t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.maxbytes)/(1024*1024),2) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024),2) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace
and t.tablespace ='APP_DATA_TABLESPACE'
returns wrong free space and hence used space too
tablespace_name= 'APP_DATA_TABLESPACE',
Totalspace size (MB)=126976
Used Space (MB) =68884
Freespace (MB)=58092
%Used =54.25
%Free = 45.75
The tablespace has no segments at present.