SET linesize 9999
SELECT T1.TABLESPACE_NAME,
T1.BYTES / 1024 / 1024 AS "bytes_used (Mb)",
T2.BYTES / 1024 / 1024 AS "bytes_free (Mb)",
T2.largest /1024 /1024 AS "largest (Mb)",
round(((T1.BYTES-T2.BYTES)/T1.BYTES)*100,2) percent_used
FROM
(
SELECT TABLESPACE_NAME,
SUM(BYTES) BYTES
FROM dba_data_files
GROUP BY TABLESPACE_NAME
)
T1,
(
SELECT TABLESPACE_NAME,
SUM(BYTES) BYTES ,
MAX(BYTES) largest
FROM dba_free_space
GROUP BY TABLESPACE_NAME
)
T2
WHERE T1.TABLESPACE_NAME=T2.TABLESPACE_NAME
--and T2.TABLESPACE_NAME like 'WKFL_%'
ORDER BY ((T1.BYTES-T2.BYTES)/T1.BYTES) DESC ;