Tablespace Usage Script
ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
100-((fr."FREE(MB)" / df."SIZE(MB)") * 100) "% USED",
RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph"
FROM
(SELECT tablespace_name,
SUM (bytes) / (1024 * 1024*1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024*1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024*1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% USED" desc
/
ablespace Size Free Used Max Ext
Name File Count (MB) (MB) (MB) (MB) % USED Graph
-------------------- ---------- --------------- --------------- --------------- ------------ ---------- -----------
SDCD 48 1,440.00 331.37 1,108.62 596 76.9881736 **
SYT 3 13.00 3.19 9.80 9 75.4230206 **
SYSAUX 3 51.37 12.73 38.64 62 75.2138295 **