Tablespace Information

Use this query to review allocated space, free space and percentage used for permanent tablespaces.

SELECT
    tablespace_name,
    ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS allocated_gb,
    ROUND(SUM(maxbytes) / 1024 / 1024 / 1024, 2) AS max_gb,
    ROUND(
        (SUM(bytes) - SUM(free_bytes))
        / NULLIF(SUM(bytes), 0) * 100,
        2
    ) AS used_pct
FROM (
    SELECT
        df.tablespace_name,
        df.bytes,
        df.maxbytes,
        NVL(fs.free_bytes, 0) AS free_bytes
    FROM dba_data_files df
    LEFT JOIN (
        SELECT
            tablespace_name,
            SUM(bytes) AS free_bytes
        FROM dba_free_space
        GROUP BY tablespace_name
    ) fs
        ON fs.tablespace_name = df.tablespace_name
)
GROUP BY tablespace_name
ORDER BY used_pct DESC;

Review datafile size, maximum size and autoextend configuration to understand how individual files are configured.

SELECT
    file_id,
    file_name,
    tablespace_name,
    ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb,
    autoextensible,
    ROUND(maxbytes / 1024 / 1024 / 1024, 2) AS max_size_gb
FROM dba_data_files
ORDER BY tablespace_name, file_id;

This query summarizes free extents recorded in DBA_FREE_SPACE and helps identify where free space is currently available.

SELECT
    tablespace_name,
    ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS free_gb,
    COUNT(*) AS free_extents
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY free_gb DESC;

Autoextend can provide additional capacity automatically. Review the maximum configured size as part of capacity planning and operational checks.

SELECT
    file_id,
    file_name,
    tablespace_name,
    ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb,
    ROUND(maxbytes / 1024 / 1024 / 1024, 2) AS max_size_gb,
    autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'
ORDER BY tablespace_name, file_id;

Compare the current datafile size with its configured maximum size to identify files with limited remaining autoextend capacity.

SELECT
    file_id,
    file_name,
    tablespace_name,
    ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb,
    ROUND(maxbytes / 1024 / 1024 / 1024, 2) AS max_size_gb,
    ROUND(
        CASE
            WHEN maxbytes > 0
            THEN bytes / maxbytes * 100
        END,
        2
    ) AS max_size_pct
FROM dba_data_files
WHERE maxbytes > 0
ORDER BY max_size_pct DESC;