SELECT
tablespace_name,
ROUND((tablespace_size * 8192) / 1024 / 1024 / 1024, 2) AS SIZE_GB,
ROUND((used_space * 8192) / 1024 / 1024 / 1024, 2) AS USED_GB,
ROUND(used_percent, 2) AS used_percent
FROM
dba_tablespace_usage_metrics;
Space used and free by tablespace (from SQL Developer)
SELECT ts.tablespace_name
, 'SQLDEV:GAUGE:0:100:0:0:'
||NVL ( ROUND ( ( ( datafile.bytes - NVL ( freespace.bytes, 0 ) ) / datafile.bytes ) * 100, 2 ), 0 ) percent_used
, ROUND ( ( ( datafile.bytes - NVL ( freespace.bytes, 0 ) ) / datafile.bytes ) * 100, 2 ) PCT_USED
, datafile.bytes / 1024 / 1024 allocated
, ROUND ( datafile.bytes / 1024 / 1024 - NVL ( freespace.bytes, 0 ) / 1024 / 1024, 2 ) used
, ROUND ( NVL ( freespace.bytes, 0 ) / 1024 / 1024, 2 ) free
, datafile.datafiles
FROM dba_tablespaces ts
, (SELECT tablespace_name
, SUM ( bytes ) bytes
FROM dba_free_space
GROUP BY tablespace_name
) freespace
, (SELECT COUNT ( 1 ) datafiles
, SUM ( bytes ) bytes
, tablespace_name
FROM dba_data_files
GROUP BY tablespace_name
) datafile
WHERE freespace.tablespace_name (+) = ts.tablespace_name
AND datafile.tablespace_name (+) = ts.tablespace_name
ORDER BY NVL ( ( ( datafile.bytes - NVL ( freespace.bytes, 0 ) ) / datafile.bytes ), 0 ) DESC
