Percentage Free Space Available

rem -----------------------------------------------------------------------
rem Filename: ts_freespace.sql
rem Purpose: Reports Percentage Tablespace Freespace Available
rem -----------------------------------------------------------------------

CLEAR
SET HEAD ON
SET VERIFY OFF
spool frt1

col tspace form a25 Heading "Tablespace"
col tot_ts_size form 99999999999999 Heading "Size (Mb)"
col free_ts_size form 99999999999999 Heading "Free (Mb)"
col ts_pct form 9999 Heading "% Free"
col ts_pct1 form 9999 Heading "% Used"
break on report
compute sum of free_ts_size on report
compute sum of tot_ts_size on report
SELECT /* + RULE */ df.tablespace_name tspace,
df.bytes/(1024*1024) tot_ts_size,
sum(fs.bytes)/(1024*1024) free_ts_size,
nvl(round(sum(fs.bytes)*100/df.bytes),1) ts_pct,
round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1
FROM dba_free_space fs
, (select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name ) df
WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes
union all
SELECT /* + RULE */
df.tablespace_name tspace,
fs.bytes/(1024*1024) tot_ts_size,
sum(df.bytes_free)/(1024*1024) free_ts_size,
nvl(round((sum(fs.bytes)- df.bytes_used) *100/fs.bytes),1) ts_pct,
round((sum(fs.BYTES) - df.BYTES_free )*100/fs.bytes) ts_pct1
FROM dba_temp_files fs
, (select tablespace_name, bytes_free, bytes_used
from V$temp_space_header
group by tablespace_name, bytes_free, bytes_used ) df
WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, fs.bytes, df.bytes_free,df.BYTES_used
order by 4 desc
/
spool off