set pagesize 50
COLUMN tablespace_name FORMAT a15 HEADING "TableSPace(KB)"
COLUMN total_size_kb FORMAT 999,999,999 HEADING "Total(KB)"
COLUMN free_size_kb FORMAT 99,999,999 HEADING "Free(KB)"
COLUMN used_size_kb FORMAT 999,999,999 HEADING "Used(KB)"
COLUMN usedpercentage FORMAT 999.99 HEADING "Used %"
COLUMN files FORMAT 99999 HEADING  "Files"
COLUMN largest FORMAT 99,999,999 HEADING  "Largest"

select
T.tablespace_name,
       TS."total_size_b"/1024 as "total_size_kb",
       NVL(FS."free_size_b"/1024, 0) as "free_size_kb",
       NVL((TS."total_size_b"-FS."free_size_b")/1024, 0) as "used_size_kb",
       NVL(Round(100 * ((TS."total_size_b"-FS."free_size_b") / TS."total_size_b"), 2), 0) as "usedpercentage",
       TS."filecnt" as "files",
       FS."maxfree" as "largest"
from
       dba_tablespaces T,
       (select tablespace_name, round (sum(bytes), 0) as "total_size_b",
               count(file_name) as "filecnt"
       from dba_data_files
       group by tablespace_name) TS,
       (select tablespace_name, round(sum(bytes), 0) as "free_size_b",
               max(bytes)/1024 as "maxfree"
       from dba_free_space
       group by tablespace_name) FS
where
       T.tablespace_name = TS.tablespace_name and
       T.tablespace_name = FS.tablespace_name (+)
order by "usedpercentage";