Query to Check Tablespace Free Size Based on Autoextend and Max Size in Oracle Database


The query now correctly calculates and displays total space, used space, free space, percentages, maximum space and free space (considering autoextend), datafile count, and autoextend status for each tablespace.

Output Sample:



Query:

SET LINESIZE 1000
COLUMN tablespace_name FORMAT A20
COLUMN total_space_mb FORMAT 999999.99
COLUMN used_space_mb FORMAT 999999.99
COLUMN free_space_mb FORMAT 999999.99
COLUMN pct_used FORMAT 999.99
COLUMN max_space_mb FORMAT 999999.99
COLUMN max_free_space_mb FORMAT 999999.99
COLUMN pct_max_used FORMAT 999.99
COLUMN datafile_count FORMAT 9999
COLUMN autoextend FORMAT A10

SELECT
    df.tablespace_name,
    ROUND(df.bytes / (1024 * 1024), 2) AS total_space_mb,
    ROUND((df.bytes - NVL(fs.bytes, 0)) / (1024 * 1024), 2) AS used_space_mb,
    ROUND(NVL(fs.bytes, 0) / (1024 * 1024), 2) AS free_space_mb,
    ROUND(((df.bytes - NVL(fs.bytes, 0)) / df.bytes) * 100, 2) AS pct_used,
    ROUND(NVL(df.maxbytes, df.bytes) / (1024 * 1024), 2) AS max_space_mb,
    ROUND((NVL(df.maxbytes, df.bytes) - (df.bytes - NVL(fs.bytes, 0))) / (1024 * 1024), 2) AS max_free_space_mb,
    ROUND(((df.bytes - NVL(fs.bytes, 0)) / NVL(df.maxbytes, df.bytes)) * 100, 2) AS pct_max_used,
    df.datafile_count,
    CASE WHEN MAX(df.autoextensible) = 'YES' THEN 'ON' ELSE 'OFF' END AS autoextend
FROM
    (SELECT
        tablespace_name,
        SUM(bytes) AS bytes,
        SUM(DECODE(autoextensible, 'YES', maxbytes, bytes)) AS maxbytes,
        COUNT(file_id) AS datafile_count,
        MAX(autoextensible) AS autoextensible
     FROM
        dba_data_files
     GROUP BY
        tablespace_name) df
LEFT JOIN
    (SELECT
        tablespace_name,
        SUM(bytes) AS bytes
     FROM
        dba_free_space
     GROUP BY
        tablespace_name) fs
ON
    df.tablespace_name = fs.tablespace_name
GROUP BY
    df.tablespace_name, df.bytes, fs.bytes, df.maxbytes , df.datafile_count
ORDER BY
    df.tablespace_name;


SELECT
    df.tablespace_name,
    ROUND(df.bytes / (1024 * 1024), 2) AS total_space_mb,
    ROUND((df.bytes - NVL(fs.bytes, 0)) / (1024 * 1024), 2) AS used_space_mb,
    ROUND(NVL(fs.bytes, 0) / (1024 * 1024), 2) AS free_space_mb,
    ROUND(((df.bytes - NVL(fs.bytes, 0)) / df.bytes) * 100, 2) AS pct_used,
    ROUND(NVL(df.maxbytes, df.bytes) / (1024 * 1024), 2) AS max_space_mb,
    ROUND((NVL(df.maxbytes, df.bytes) - (df.bytes - NVL(fs.bytes, 0))) / (1024 * 1024), 2) AS max_free_space_mb,
    ROUND(((df.bytes - NVL(fs.bytes, 0)) / NVL(df.maxbytes, df.bytes)) * 100, 2) AS pct_max_used,
    df.datafile_count,
    CASE WHEN MAX(df.autoextensible) = 'YES' THEN 'ON' ELSE 'OFF' END AS autoextend
FROM
    (SELECT
        tablespace_name,
        SUM(bytes) AS bytes,
        SUM(DECODE(autoextensible, 'YES', maxbytes, bytes)) AS maxbytes,
        COUNT(file_id) AS datafile_count,
        MAX(autoextensible) AS autoextensible
     FROM
        dba_data_files
     GROUP BY
        tablespace_name) df
LEFT JOIN
    (SELECT
        tablespace_name,
        SUM(bytes) AS bytes
     FROM
        dba_free_space
     GROUP BY
        tablespace_name) fs
ON
    df.tablespace_name = fs.tablespace_name
WHERE
    ROUND((NVL(df.maxbytes, df.bytes) - (df.bytes - NVL(fs.bytes, 0))) / (1024 * 1024), 2) < (0.20 * ROUND(NVL(df.maxbytes, df.bytes) / (1024 * 1024), 2))  -- Only tablespaces with less than 20% free
    AND df.tablespace_name NOT IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO')  -- Exclude UNDO tablespaces
GROUP BY
    df.tablespace_name, df.bytes, fs.bytes, df.maxbytes, df.datafile_count
ORDER BY
    pct_used DESC;









Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment