Query to display tablespace usage details: size, used space, free space, and percentage used

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;