Tablespace Usage Query
We are getting different result while we are trying to find the tablespace usage with below query for one tablespace.
Query 1:-
select df.tablespace_name "Tablespace",
totalusedspace "Used GB",
(df.totalspace - tu.totalusedspace) "Free GB",
df.totalspace "Total GB",
(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
(sum(bytes) / (1024*1024*1024)) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select (sum(bytes)/(1024*1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name and df.tablespace_name in('XYZ');
Query 2:-
select b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB , tbs_size - a.free_space UsedGB