Query to display tablespace usage details: size, used space, free space, and percentage used
SELECT
Substr(df.tablespace_name, 1, 20) "Tablespace Name", -- Tablespace name (first 20 characters)
Substr(df.file_name, 1, 40) "File Name", -- Datafile name (first 40 characters)
Round(df.bytes / 1024 / 1024, 2) "Size (M)", -- Total size of the datafile in MB
Round(e.used_bytes / 1024 / 1024, 2) "Used (M)", -- Used space in the datafile in MB
Round(f.free_bytes / 1024 / 1024, 2) "Free (M)", -- Free space in the datafile in MB
Rpad( -- ASCII-based progress bar to represent % used
' ' || Rpad('X', Round(e.used_bytes * 10 / df.bytes, 0), 'X'),
11,
'-'
) "% Used"
FROM
DBA_DATA_FILES df -- Datafile metadata from DBA view
LEFT OUTER JOIN (
-- Aggregating used space from dba_extents per file
SELECT
file_id,
Sum(Decode(bytes, NULL, 0, bytes)) used_bytes
FROM
dba_extents
GROUP BY
file_id
) e ON e.file_id = df.file_id
LEFT OUTER JOIN (
-- Getting maximum free extent size per file from dba_free_space
SELECT
Max(bytes) free_bytes,
file_id
FROM
dba_free_space
GROUP BY
file_id
) f ON f.file_id = df.file_id
ORDER BY
df.tablespace_name,
df.file_name;
