DBA_FREE_SPACE - Check Available Tablespace Space

DBA_FREE_SPACE

Overview

DBA_FREE_SPACE contains one row for every contiguous free chunk within every tablespace in the database. When a segment releases extents — through a DROP, TRUNCATE, or SHRINK — those blocks become free and appear here. DBAs query this view to determine how much space is available in each tablespace, to assess fragmentation (many small free chunks vs few large ones), and to join with DBA_DATA_FILES to calculate used percentage. Note that temporary tablespace free space is tracked separately and does not appear in DBA_FREE_SPACE.

View Type: Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_FREE_SPACE or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
TABLESPACE_NAME	VARCHAR2(30)	Name of the tablespace containing this free chunk
FILE_ID	NUMBER	Datafile identifier in which this free chunk resides; joins to DBA_DATA_FILES.FILE_ID
BLOCK_ID	NUMBER	Starting block number of this free chunk within the datafile
BYTES	NUMBER	Size of this free chunk in bytes
BLOCKS	NUMBER	Size of this free chunk in Oracle blocks
RELATIVE_FNO	NUMBER	Relative file number within the tablespace; used in join with DBA_EXTENTS for physical layout analysis

Essential Queries

Free Space Per Tablespace

Summarise available free space per tablespace with total free bytes and the number of separate free chunks:

SELECT
    f.tablespace_name,
    COUNT(*)                                    AS free_chunks,
    ROUND(SUM(f.bytes)   / 1073741824, 3)       AS free_gb,
    ROUND(MAX(f.bytes)   / 1073741824, 3)       AS largest_chunk_gb,
    ROUND(MIN(f.bytes)   / 1048576,    3)       AS smallest_chunk_mb,
    ROUND(AVG(f.bytes)   / 1048576,    2)       AS avg_chunk_mb
FROM
    dba_free_space f
GROUP BY
    f.tablespace_name
ORDER BY
    free_gb DESC;

Tablespace Usage Percentage

Join DBA_FREE_SPACE with DBA_DATA_FILES to produce a complete used vs free report with percentage utilisation:

SELECT
    d.tablespace_name,
    ROUND(SUM(d.bytes)              / 1073741824, 2)    AS allocated_gb,
    ROUND(NVL(SUM(f.free_bytes), 0) / 1073741824, 2)    AS free_gb,
    ROUND(
        (SUM(d.bytes) - NVL(SUM(f.free_bytes), 0))
        / 1073741824, 2)                                 AS used_gb,
    ROUND(
        (SUM(d.bytes) - NVL(SUM(f.free_bytes), 0))
        / SUM(d.bytes) * 100, 1)                         AS pct_used,
    ROUND(NVL(SUM(f.free_bytes), 0)
        / SUM(d.bytes) * 100, 1)                         AS pct_free,
    SUM(CASE WHEN d.autoextensible = 'YES' THEN 1 ELSE 0 END) AS autoext_files
FROM
    dba_data_files d
    LEFT JOIN (
        SELECT tablespace_name, SUM(bytes) AS free_bytes
        FROM   dba_free_space
        GROUP BY tablespace_name
    ) f ON f.tablespace_name = d.tablespace_name
GROUP BY
    d.tablespace_name
ORDER BY
    pct_used DESC NULLS LAST;

Fragmentation Analysis

Count free chunks per tablespace — a tablespace with hundreds of tiny free chunks is fragmented and may fail to satisfy large extent requests even though total free bytes looks healthy:

SELECT
    f.tablespace_name,
    COUNT(*)                                    AS free_chunk_count,
    ROUND(SUM(f.bytes)   / 1073741824, 3)       AS total_free_gb,
    ROUND(MAX(f.bytes)   / 1073741824, 3)       AS largest_chunk_gb,
    ROUND(AVG(f.bytes)   / 1048576,    2)       AS avg_chunk_mb,
    t.extent_management,
    t.allocation_type,
    CASE
        WHEN COUNT(*) > 1000 THEN 'HIGH FRAGMENTATION'
        WHEN COUNT(*) > 200  THEN 'MODERATE FRAGMENTATION'
        ELSE                      'ACCEPTABLE'
    END                                         AS fragmentation_status
FROM
    dba_free_space   f
    JOIN dba_tablespaces t ON t.tablespace_name = f.tablespace_name
GROUP BY
    f.tablespace_name,
    t.extent_management,
    t.allocation_type
ORDER BY
    free_chunk_count DESC;

Comprehensive Tablespace Report with Autoextend Headroom

Produce an all-in-one storage report showing current usage, free space, and potential maximum size including autoextend capacity — suitable for scheduled monitoring jobs:

SELECT
    d.tablespace_name,
    t.contents,
    t.status,
    ROUND(SUM(d.bytes)              / 1073741824, 2)    AS allocated_gb,
    ROUND(NVL(SUM(f.free_bytes), 0) / 1073741824, 2)    AS free_gb,
    ROUND(
        (SUM(d.bytes) - NVL(SUM(f.free_bytes), 0))
        / 1073741824, 2)                                 AS used_gb,
    ROUND(
        (SUM(d.bytes) - NVL(SUM(f.free_bytes), 0))
        / SUM(d.bytes) * 100, 1)                         AS pct_used,
    ROUND(
        SUM(
            CASE WHEN d.autoextensible = 'YES'
                 THEN d.maxbytes - d.bytes
                 ELSE 0
            END
        ) / 1073741824, 2)                               AS autoext_headroom_gb,
    ROUND(
        (SUM(d.bytes)
         + SUM(CASE WHEN d.autoextensible = 'YES' THEN d.maxbytes - d.bytes ELSE 0 END))
        / 1073741824, 2)                                 AS total_possible_gb
FROM
    dba_data_files   d
    JOIN dba_tablespaces t ON t.tablespace_name = d.tablespace_name
    LEFT JOIN (
        SELECT tablespace_name, SUM(bytes) AS free_bytes
        FROM   dba_free_space
        GROUP BY tablespace_name
    ) f ON f.tablespace_name = d.tablespace_name
GROUP BY
    d.tablespace_name,
    t.contents,
    t.status
ORDER BY
    pct_used DESC NULLS LAST;

Free Chunks in a Specific File

Drill into per-file free space to understand exactly where free blocks are located within a nearly full tablespace:

SELECT
    f.tablespace_name,
    f.file_id,
    d.file_name,
    f.block_id                                  AS start_block,
    f.blocks                                    AS free_blocks,
    ROUND(f.bytes / 1048576, 2)                 AS free_mb,
    f.relative_fno
FROM
    dba_free_space  f
    JOIN dba_data_files d ON d.file_id = f.file_id
WHERE
    f.tablespace_name = :tablespace_name
ORDER BY
    f.file_id,
    f.block_id;

Common Use Cases

• Daily health monitoring — Include the usage percentage query in automated monitoring scripts to alert when any tablespace exceeds a configured threshold (typically 85%)
• Pre-load capacity checks — Before a large data load or index rebuild, verify the target tablespace has a contiguous free chunk large enough to satisfy the initial extent requirement
• Fragmentation diagnosis — When ORA-01652 or ORA-01653 occurs despite apparently sufficient free space, query free chunk count to confirm fragmentation is preventing large extent allocations
• Tablespace shrink candidates — Find tablespaces with very large free_gb relative to used_gb, indicating over-allocated space that could be reclaimed by resizing datafiles
• Autoextend planning — Combine with DBA_DATA_FILES to calculate how much autoextend headroom remains before a tablespace hits its hard maximum ceiling
• ORA-01653 / ORA-01654 root cause — Immediately after a space extension failure, query this view to confirm whether free space truly is exhausted or just fragmented

Related Views

• DBA_TABLESPACES — Tablespace configuration; join on TABLESPACE_NAME to add contents type, extent management policy, and encryption status
• DBA_DATA_FILES — Datafile sizes and autoextend settings; the essential join partner for used vs free percentage calculations
• DBA_SEGMENTS — All allocated segments; the complement to DBA_FREE_SPACE — everything that is not free is a segment
• DBA_TEMP_FILES — Temporary tablespace files; temp free space is not in DBA_FREE_SPACE but in V$TEMP_SPACE_HEADER
• V$SORT_USAGE — Active temp segment allocations by session; relevant when temporary tablespace appears full
• DBA_TABLESPACE_USAGE_METRICS — Simplified used/free summary view available from Oracle 11g that aggregates the same data as the DBA_FREE_SPACE + DBA_DATA_FILES join

Version Notes

• Oracle 7 / 8: DBA_FREE_SPACE has been available since Oracle 7; with dictionary-managed tablespaces, free space is tracked in FET$ (the free extent table)
• Oracle 8i: Locally managed tablespaces introduced; free space is tracked via bitmaps in the file header rather than the data dictionary, but DBA_FREE_SPACE still reflects the available space
• Oracle 9i: UNIFORM allocation for locally managed tablespaces ensures free chunks are always a consistent size, preventing fragmentation; DBA_FREE_SPACE queries become simpler to interpret
• Oracle 10g: Automatic Segment Space Management (ASSM) with locally managed tablespaces became the standard; fragmentation of DBA_FREE_SPACE almost entirely eliminated for such tablespaces
• Oracle 11g: DBA_TABLESPACE_USAGE_METRICS introduced as a pre-aggregated alternative to the DBA_FREE_SPACE + DBA_DATA_FILES join
• Oracle 12c (Multitenant): PDB-scoped queries see only tablespaces belonging to the current PDB; CDB root sees all containers
• Oracle 23ai: No structural changes; DBA_FREE_SPACE remains the authoritative source for permanent tablespace free space