define tsname
undefine tsname
# Put your tablespace name in here
define tsname=”%USER%”
select
df.tablespace_name,
fs.GB_FS_BefAuto,
df.GB_Tot_Space_BefAuto “Total Space Allocated”,
round(100*(fs.GB_FS_BefAuto/df.GB_Tot_Space_BefAuto),2) “Percent Free”,
df.GB_Tot_MaxBytes “MaxBytes To Allocate”,GB_Rem_ToBe_AutoAllocated “Remaining To AutoAllocate”
from
(select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) GB_FS_BefAuto
from dba_free_space
where tablespace_name like ‘&&tsname’
group by tablespace_name) fs,
(select tablespace_name, round(sum(bytes)/(1024*1024*1024),2) GB_Tot_Space_BefAuto,
round(sum(decode(autoextensible,’YES’,MAXBYTES,’NO’,BYTES)) / (1024*1024*1024), 2) GB_Tot_MaxBytes,
round(sum(decode(autoextensible,’YES’,MAXBYTES,’NO’,BYTES)-BYTES)/ (1024*1024*1024), 2) GB_Rem_ToBe_AutoAllocated
from dba_data_files
where tablespace_name like ‘&&tsname’
group by tablespace_name) df
where df.tablespace_name like ‘&&tsname’
and fs.tablespace_name=df.tablespace_name
order by df.tablespace_name;