How to check tablespace size and free space in Oracle?

Use the DBA_DATA_FILES and DBA_FREE_SPACE join to get total, used, and free space per tablespace in GB with percentage free; 2. Improve accuracy with a LEFT JOIN from DBA_TABLESPACES to include fully used tablespaces by using NVL to handle null free space; 3. Query DBA_TEMP_FILES and V$TEMP_SPACE_HEADER separately to include temporary tablespace sizes and usage; 4. For a quick overview, run simple GROUP BY queries on DBA_DATA_FILES and DBA_FREE_SPACE for size and free space in MB; always ensure DBA privileges for full access and consider autoextensible files when evaluating potential growth, which can be checked via the AUTOEXTENSIBLE and MAXBYTES columns in DBA_DATA_FILES, and use monitoring tools for trend analysis.

To check tablespace size and free space in Oracle, you can use SQL queries that pull information from data dictionary views like DBA_DATA_FILES, DBA_FREE_SPACE, and DBA_TABLESPACES. Here’s how to do it step by step.

✅ 1. Check Total Size, Used Space, and Free Space for Each Tablespace

Run this query to get a comprehensive overview:

SELECT 
    fs.tablespace_name,
    ROUND(SUM(df.bytes) / 1024 / 1024 / 1024, 2) AS "Total Size (GB)",
    ROUND(SUM(fs.bytes) / 1024 / 1024 / 1024, 2) AS "Free Space (GB)",
    ROUND((SUM(df.bytes) - SUM(fs.bytes)) / 1024 / 1024 / 1024, 2) AS "Used Space (GB)",
    ROUND((SUM(fs.bytes) / SUM(df.bytes)) * 100, 2) AS "Pct Free (%)"
FROM 
    dba_free_space fs,
    dba_data_files df
WHERE 
    fs.tablespace_name = df.tablespace_name
GROUP BY 
    fs.tablespace_name;

? Note: This shows only tablespaces with datafiles. If a tablespace has no free space entries, it might not appear — so better to use an outer join or DBA_TABLESPACES.

✅ 2. More Accurate Query Using Outer Join (Includes All Tablespaces)

This version ensures even tablespaces with no free space are included:

SELECT 
    dt.tablespace_name,
    ROUND(SUM(df.bytes) / 1024 / 1024 / 1024, 2) AS "Total Size (GB)",
    ROUND(SUM(NVL(fs.bytes, 0)) / 1024 / 1024 / 1024, 2) AS "Free Space (GB)",
    ROUND((SUM(df.bytes) - SUM(NVL(fs.bytes, 0))) / 1024 / 1024 / 1024, 2) AS "Used Space (GB)",
    ROUND((SUM(NVL(fs.bytes, 0)) / SUM(df.bytes)) * 100, 2) AS "Pct Free (%)"
FROM 
    dba_tablespaces dt
    JOIN dba_data_files df ON dt.tablespace_name = df.tablespace_name
    LEFT JOIN dba_free_space fs ON dt.tablespace_name = fs.tablespace_name
GROUP BY 
    dt.tablespace_name;

? NVL(fs.bytes, 0) handles cases where there's no free space entry (e.g., fully used tablespace).

✅ 3. Include Temporary Tablespaces

The above queries don't cover temporary tablespaces. For temp tablespaces, use:

SELECT 
    tablespace_name,
    ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS "Total Size (GB)"
FROM 
    dba_temp_files
GROUP BY 
    tablespace_name;

And to check temp tablespace usage (requires querying V$TEMP_SPACE_HEADER):

SELECT 
    tablespace_name,
    ROUND(SUM(bytes_cached) / 1024 / 1024 / 1024, 2) AS "Total Size (GB)",
    ROUND(SUM(bytes_used) / 1024 / 1024 / 1024, 2) AS "Used Space (GB)",
    ROUND(SUM(bytes_free) / 1024 / 1024 / 1024, 2) AS "Free Space (GB)"
FROM 
    v$temp_space_header
GROUP BY 
    tablespace_name;

✅ 4. Quick One-Liner for Immediate Overview

If you just want a fast summary:

SELECT 
    tablespace_name, 
    SUM(bytes) / 1024 / 1024 AS "Size_MB"
FROM 
    dba_data_files 
GROUP BY 
    tablespace_name;

-- And free space:
SELECT 
    tablespace_name, 
    SUM(bytes) / 1024 / 1024 AS "Free_MB"
FROM 
    dba_free_space 
GROUP BY 
    tablespace_name;

You can combine them later manually or in a script.

?️ Tips

• You need DBA privileges to access DBA_* views.
• Use USER_TABLESPACES or ALL_TABLESPACES if you don’t have DBA access (but info may be limited).
• Consider monitoring growth trends over time using AWR or custom logging.
• Watch out for autoextensible datafiles — total possible size may be larger than current size.

You can check autoextension with:

SELECT 
    file_name, 
    tablespace_name, 
    bytes / 1024 / 1024 AS "Current Size (MB)",
    maxbytes / 1024 / 1024 AS "Max Size (MB)",
    autoextensible
FROM 
    dba_data_files;

Basically, the key is combining DBA_DATA_FILES for total allocated space and DBA_FREE_SPACE for unused blocks. Doesn’t require external tools — just SQL*Plus or any Oracle client.

The above is the detailed content of How to check tablespace size and free space in Oracle?. For more information, please follow other related articles on the PHP Chinese website!