with 
 ts as (select/*+ materialize */ name,ts#,bitmapped,online$,contents$,blocksize,flags from sys.ts$)
,fi as (select/*+ materialize */ * from sys.file$)
,ktfbfe as (select/*+ materialize */ * from sys.x$ktfbfe)
,free_space(tablespace_name, bytes) as (
   select ts.name, 
          sum(f.length) * ts.blocksize
   from ts, sys.fet$ f
   where ts.ts# = f.ts#
     and ts.bitmapped = 0
   group by ts.name,ts.blocksize
   union all
   select
          ts.name,
          sum(f.ktfbfeblks) * ts.blocksize
   from ts, ktfbfe f
   where ts.ts# = f.ktfbfetsn
     and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
     and bitand(ts.flags, 4503599627370496) <> 4503599627370496
   group by ts.name,ts.blocksize
   union all
   select
          ts.name,
          sum(u.ktfbueblks) * ts.blocksize
   from sys.recyclebin$ rb, ts, sys.x$ktfbue u, fi
   where ts.ts# = rb.ts#
     and rb.ts# = fi.ts#
     and u.ktfbuefno = fi.relfile#
     and u.ktfbuesegtsn = rb.ts#
     and u.ktfbuesegfno = rb.file#
     and u.ktfbuesegbno = rb.block#
     and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
     and bitand(ts.flags, 4503599627370496) <> 4503599627370496
   group by ts.name,ts.blocksize
   union all
   select ts.name,
          sum(u.length) * ts.blocksize
   from ts, sys.uet$ u, fi, sys.recyclebin$ rb
   where ts.ts# = u.ts#
     and u.ts# = fi.ts#
     and u.segfile# = fi.relfile#
     and u.ts# = rb.ts#
     and u.segfile# = rb.file#
     and u.segblock# = rb.block#
     and ts.bitmapped = 0
   group by ts.name,ts.blocksize
   union all
   select
          ts.name,
          sum(f.extent_length_blocks_2K) *2048
   from ts, sys.new_lost_write_extents$ f, fi
   where ts.ts# = f.extent_datafile_tsid
     and f.extent_datafile_tsid = fi.ts#
     and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
     and bitand(ts.flags, 4503599627370496) = 4503599627370496
   group by ts.name
   )
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
/

with
 ts as (select/*+ materialize */ name,ts#,bitmapped,online$,contents$,blocksize,flags from sys.ts$)
,fi as (select/*+ materialize */ * from sys.file$)
,ktfbfe as (select/*+ materialize */ * from sys.x$ktfbfe)
,free_space(tablespace_name, bytes) as (
      select ts.name, 
             sum(f.length) * ts.blocksize
      from ts, sys.fet$ f
      where ts.ts# = f.ts#
        and ts.bitmapped = 0
      group by ts.name,ts.blocksize
      union all
      select
             ts.name,
             sum(f.ktfbfeblks) * ts.blocksize
      from ts, ktfbfe f
      where ts.ts# = f.ktfbfetsn
        and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
      group by ts.name,ts.blocksize
      union all
      select
             ts.name, 
             sum(u.ktfbueblks) * ts.blocksize
      from sys.recyclebin$ rb, ts, sys.x$ktfbue u, fi
      where ts.ts# = rb.ts#
        and rb.ts# = fi.ts#
        and u.ktfbuefno = fi.relfile#
        and u.ktfbuesegtsn = rb.ts#
        and u.ktfbuesegfno = rb.file#
        and u.ktfbuesegbno = rb.block#
        and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
      group by ts.name,ts.blocksize
      union all
      select ts.name, 
             sum(u.length) * ts.blocksize
      from ts, sys.uet$ u, fi, sys.recyclebin$ rb
      where ts.ts# = u.ts#
        and u.ts# = fi.ts#
        and u.segfile# = fi.relfile#
        and u.ts# = rb.ts#
        and u.segfile# = rb.file#
        and u.segblock# = rb.block#
        and ts.bitmapped = 0
      group by ts.name,ts.blocksize
)
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
      from free_space
      group by tablespace_name
     ) a,
     (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
      from dba_data_files
      group by tablespace_name
     ) b
where a.tablespace_name=b.tablespace_name

with
 ts as (select/*+ materialize */ name,ts#,bitmapped,online$,contents$,blocksize,flags from sys.ts$)
,fi as (select/*+ materialize */ * from sys.file$)
,ktfbfe as (select/*+ materialize */ * from sys.x$ktfbfe)
,free_space(tablespace_name, bytes) as (
      select ts.name, 
             sum(f.length) * ts.blocksize
      from ts, sys.fet$ f
      where ts.ts# = f.ts#
        and ts.bitmapped = 0
      group by ts.name,ts.blocksize
      union all
      select
             ts.name,
             sum(f.ktfbfeblks) * ts.blocksize
      from ts, ktfbfe f
      where ts.ts# = f.ktfbfetsn
        and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
      group by ts.name,ts.blocksize
      union all
      select
             ts.name, 
             sum(u.ktfbueblks) * ts.blocksize
      from sys.recyclebin$ rb, ts, sys.x$ktfbue u, fi
      where ts.ts# = rb.ts#
        and rb.ts# = fi.ts#
        and u.ktfbuefno = fi.relfile#
        and u.ktfbuesegtsn = rb.ts#
        and u.ktfbuesegfno = rb.file#
        and u.ktfbuesegbno = rb.block#
        and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
      group by ts.name,ts.blocksize
      union all
      select ts.name, 
             sum(u.length) * ts.blocksize
      from ts, sys.uet$ u, fi, sys.recyclebin$ rb
      where ts.ts# = u.ts#
        and u.ts# = fi.ts#
        and u.segfile# = fi.relfile#
        and u.ts# = rb.ts#
        and u.segfile# = rb.file#
        and u.segblock# = rb.block#
        and ts.bitmapped = 0
      group by ts.name,ts.blocksize
)
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
      from free_space
      group by tablespace_name
     ) a,
     (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
      from dba_data_files
      group by tablespace_name
     ) b
where a.tablespace_name=b.tablespace_name;

with 
 fi as (select/*+ materialize */ * from sys.file$)
,used as (select/*+ materialize */ fi.ts#,sum(blocks) blocks from fi group by fi.ts#)
,free as (
   select ts#,sum(blocks) blocks
   from (
      select f.ts#,
             sum(f.length) blocks
      from sys.fet$ f
      group by f.ts#
      union all
      select
             f.ktfbfetsn,
             sum(f.ktfbfeblks)
      from sys.x$ktfbfe f
      group by f.ktfbfetsn
      union all
      select
             rb.ts#, 
             sum(u.ktfbueblks)
      from sys.recyclebin$ rb, sys.x$ktfbue u, fi
      where rb.ts# = fi.ts#
        and u.ktfbuefno = fi.relfile#
        and u.ktfbuesegtsn = rb.ts#
        and u.ktfbuesegfno = rb.file#
        and u.ktfbuesegbno = rb.block#
      group by rb.ts#
      union all
      select u.ts#, 
             sum(u.length)
      from sys.uet$ u, fi, sys.recyclebin$ rb
      where u.ts# = fi.ts#
        and u.segfile# = fi.relfile#
        and u.ts# = rb.ts#
        and u.segfile# = rb.file#
        and u.segblock# = rb.block#
      group by u.ts#
  )
  group by ts#
)
select t.name
      ,free.blocks*blocksize as free_space
      ,used.blocks*blocksize 
from sys.ts$ t,free,used
where t.ts#=free.ts#
  and t.ts#=used.ts#
/