SCRIPT:To see the free available space in TABLESPACE’s

Free Space In Tablespace:

col tablespace_name format a20
col "size GB" format 999,999,999
col "% Used" format 999
col "free GB" format 99,999,999
select tsu.tablespace_name, ceil(tsu.used_gb) "size GB",
decode(ceil(tsf.free_gb), NULL, 0, ceil(tsf.free_gb)) "free GB",
decode(100 - ceil(tsf.free_gb/tsu.used_gb*100), NULL,
100,100 - ceil(tsf.free_gb/tsu.used_gb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024/1024 used_gb
from dba_data_files
group by tablespace_name
union all
select tablespace_name ,sum(bytes)/1024/1024/1024 used_gb
from dba_temp_files group by tablespace_name) tsu,
(select tablespace_name, sum(bytes)/1024/1024/1024 free_gb
from dba_free_space group by tablespace_name) tsf
where
tsu.tablespace_name = tsf.tablespace_name (+)
order by 4;
----TO Check Allocated Space and Quota by User:

select * from DBA_TS_QUOTAS
order by Tablespace_Name, Username;