Temp Tablespace

select distinct(a.tablespace_name), b.total_size ,b.used_size - c.free_space used_size,b.space_available + c.free_space SPACE_AVAILABLE
from dba_tables a,
(select sum(MAXBYTES)/1024/1024/1024 total_size, sum(BYTES)/1024/1024/1024 used_size, sum(MAXBYTES)/1024/1024/1024 - sum(BYTES)/1024/1024/1024 space_available, TABLESPACE_NAME
FROM DBA_DATA_FILES
group by TABLESPACE_NAME
) b,
(select
ddf.tablespace_name
,sum(dfs.bytes)/1024/1024/1024 free_space
from dba_data_files ddf, dba_free_space dfs
where ddf.file_id = dfs.file_id
group by ddf.tablespace_name ) c
where
a.tablespace_name=b.tablespace_name
and a.tablespace_name=c.tablespace_name
--and a.tablespace_name like '%SRCSTGP04%' –if tablespace is known then mention here.
order by 4;