--
--  data_files_freespace_show.sql
--
--  Description: Data files - Display free space for all data_files
--
--  Compatability: 7.3.x and above
--
--  Usage: @data_files_freespace_show
--
--
-- save SQL*Plus settings:
store set savesqlplus_settings.sql replace
--
-- set SQL*Plus settings:
set linesize 200
set pages 40
--
break on report
compute sum of tot_KB tot_freeKB on report
col tablespace_name format A30 heading tablespace
col file_name format A60
col tot_KB format 999,999,999,999
col tot_freeKB format  999,999,999
col Kb_max format  999,999,999

select f.tablespace_name
      ,f.file_name
      ,f.bytes/1024 tot_KB
      ,sum(nvl(s.bytes,0)/1024) tot_freeKB
      ,max(nvl(s.bytes,0)/1024) maxextent_freeKB
      ,( (sum(nvl(s.bytes,0)/1024)) / (f.bytes/1024) )*100 Pct_available
from   sys.dba_data_files f
      ,sys.dba_free_space s
where  f.file_id = s.file_id(+)
group by f.tablespace_name, f.file_name, f.bytes/1024
order by 1,2
;
--
-- restore SQL*Plus settings:
@savesqlplus_settings