ora_datafile_free_info.sql

set verify off
set lines 300
col file_name for a120
select dfs.tablespace_name
      ,dfs.file_id
      ,dfs.relative_fno      
      ,ddf.file_name
      ,sum(dfs.bytes) as "FREE_BYTES"
      ,sum(dfs.bytes/1024/1024) as "FREE_MB"
      ,sum(dfs.blocks) "BLOCKS_QTY"
      ,dts.block_size
from dba_free_space dfs
    ,dba_data_files ddf
    ,dba_tablespaces dts
where dfs.tablespace_name = ddf.tablespace_name
  and dfs.file_id = ddf.file_id
  and dfs.relative_fno = ddf.relative_fno
  and dfs.tablespace_name = upper('&1')
  and dts.tablespace_name = ddf.tablespace_name
group by dfs.tablespace_name
        ,dfs.file_id
        ,dfs.relative_fno
        ,ddf.file_name
        ,dts.block_size
order by dfs.tablespace_name
        ,dfs.file_id;
undefine 1

set verify off
set lines 300
col file_name for a120
select dfs.tablespace_name
      ,dfs.file_id
      ,dfs.relative_fno      
      ,ddf.file_name
      ,sum(dfs.bytes) as "FREE_BYTES"
      ,sum(dfs.bytes/1024/1024) as "FREE_MB"
      ,sum(dfs.blocks) "BLOCKS_QTY"
      ,dts.block_size
from dba_free_space dfs
    ,dba_data_files ddf
    ,dba_tablespaces dts
where dfs.tablespace_name = ddf.tablespace_name
  and dfs.file_id = ddf.file_id
  and dfs.relative_fno = ddf.relative_fno
  and dfs.tablespace_name = upper('&1')
  and dts.tablespace_name = ddf.tablespace_name
group by dfs.tablespace_name
        ,dfs.file_id
        ,dfs.relative_fno
        ,ddf.file_name
        ,dts.block_size
order by dfs.tablespace_name
        ,dfs.file_id;
undefine 1