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;


Custom Query to check based on free space or percent utilization

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
    (
        -- Check only max free space if tablespace max size is > 1TB
        (ROUND(NVL(df.maxbytes, df.bytes) / (1024 * 1024 * 1024), 2) > 1024 AND
         ROUND((NVL(df.maxbytes, df.bytes) - (df.bytes - NVL(fs.bytes, 0))) / (1024 * 1024), 2) < 50 * 1024)
        OR
-- Otherwise, check percentage free space for tablespaces with max size <= 1TB
        (ROUND(NVL(df.maxbytes, df.bytes) / (1024 * 1024 * 1024), 2) <= 1024 AND
         ROUND((NVL(df.maxbytes, df.bytes) - (df.bytes - NVL(fs.bytes, 0))) / (1024 * 1024), 2) < (0.10 * ROUND(NVL(df.maxbytes, df.bytes) / (1024 * 1024), 2)))
    )
    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;


  • Tablespaces with a max size ≥ 1 TB. 
    • If max free space < 50 GB, check them.
    • If max free space ≥ 50 GB, skip percentage-based checks.
  • For tablespaces with max size < 1 TB, check if the percentage utilized is below 10% free.

Optimized version of same query

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 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 
    -- Condition for tablespaces with max size ≥ 1 TB
    (
        ROUND(df.maxbytes / (1024 * 1024 * 1024), 2) < 1024  -- If max size < 1 TB, check % free
        AND ROUND((NVL(df.maxbytes, df.bytes) - (df.bytes - NVL(fs.bytes, 0))) / df.maxbytes * 100, 2) < 10
    )
    OR
    (
        ROUND(df.maxbytes / (1024 * 1024 * 1024), 2) >= 1024  -- If max size ≥ 1 TB
        AND ROUND((NVL(df.maxbytes, df.bytes) - (df.bytes - NVL(fs.bytes, 0))) / (1024 * 1024), 2) < 50 * 1024 -- Check only if max free < 50 GB
    )
    -- Exclude UNDO tablespaces
    AND df.tablespace_name NOT IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO')
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