select
  ts.name,
  decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL')               EXT_MANAGE,
  df.file_count                                                FILE_COUNT,
  trunc(df.total_bytes/(1024*1024),0)                               TOTAL,
  trunc((nvl(fs.free_count,0)),0)                                   FREE_COUNT,
  trunc(((df.total_bytes) - nvl(fs.free_extents,0))/(1024*1024),0)  USED,
  trunc(nvl(fs.free_extents,0)/(1024*1024),0)                       FREE_EXTENTS,
  trunc(nvl(fs.max_free,0)/(1024*1024),0)                           MAX_FREE,
  trunc((nvl(fs.free_extents,0)/df.total_bytes)*100,1)        "FREE(%)" 
from
  sys.ts$ ts,
  (select 
     tablespace_name,
     count(user_bytes)    FILE_COUNT,
     sum(user_bytes)      TOTAL_BYTES
   from
   dba_data_files 
   group by tablespace_name
  ) df, 
  (select
     tablespace_name,
     count(fs.bytes) FREE_COUNT,
     sum(fs.bytes)   FREE_EXTENTS,
     max(fs.bytes)   MAX_FREE
   from 
      dba_free_space fs
   group by tablespace_name
   ) fs
where 
  ts.name = fs.tablespace_name(+) and
  ts.name = df.tablespace_name and
  df.file_count !=0 
order by 9,1 
