/*
 *  freespace in tablespace and extents
 */

set pages 999
set linesize 200
col tablespace_name for a20
col free_MB for 9999999.9
col used_MB for 9999999.9
col "FREE_%" for 99.9

SELECT vt.ts#, t.tablespace_name, f.free_MB  free_MB, (f.free_MB/(t.size_MB/100))  "FREE_%",  t.size_MB, (t.size_MB - f.free_MB) used_MB
FROM v$tablespace vt,
     (select tablespace_name, sum(bytes)/1024/1024 free_MB from dba_free_space group by tablespace_name) f,
     (select tablespace_name, sum(bytes)/1024/1024 size_MB from dba_data_files group by tablespace_name) t
WHERE f.tablespace_name=t.tablespace_name and vt.name = t.tablespace_name
ORDER BY 4,2
/