DBA_SEGMENTS - Find Oracle Object Sizes & Space Usage

DBA_SEGMENTS

Overview

DBA_SEGMENTS contains one row for every segment allocated in the database — tables, indexes, LOBs, rollback segments, clusters, and more. It is the definitive source for understanding how much physical space each database object actually occupies on disk. Unlike DBA_TABLES or DBA_INDEXES, which store only statistics-based estimates, DBA_SEGMENTS reflects the true allocation recorded in the data dictionary. DBAs use it to find space hogs, plan purges, identify fragmented objects, and produce accurate chargeback reports by schema or tablespace.

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

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema owning the segment
SEGMENT_NAME	VARCHAR2(128)	Name of the segment (table name, index name, etc.)
PARTITION_NAME	VARCHAR2(128)	Partition or subpartition name for partitioned objects; NULL for non-partitioned
SEGMENT_TYPE	VARCHAR2(18)	Type: TABLE, TABLE PARTITION, INDEX, INDEX PARTITION, LOB, LOB PARTITION, ROLLBACK, TYPE2 UNDO, CLUSTER, LOBINDEX, NESTED TABLE, etc.
TABLESPACE_NAME	VARCHAR2(30)	Tablespace in which this segment resides
BYTES	NUMBER	Total bytes currently allocated to this segment
BLOCKS	NUMBER	Total Oracle blocks currently allocated to this segment
EXTENTS	NUMBER	Number of extents allocated to this segment
INITIAL_EXTENT	NUMBER	Size in bytes of the first extent when the segment was created
NEXT_EXTENT	NUMBER	Size in bytes of the next extent to be allocated (NULL for locally managed tablespaces using SYSTEM allocation)

Essential Queries

Top 20 Segments by Size

Identify the largest objects in the database regardless of schema or tablespace — the most common first step in a space investigation:

SELECT
    s.owner,
    s.segment_name,
    s.partition_name,
    s.segment_type,
    s.tablespace_name,
    ROUND(s.bytes   / 1073741824, 3)    AS size_gb,
    s.extents,
    s.blocks
FROM
    dba_segments s
ORDER BY
    s.bytes DESC
FETCH FIRST 20 ROWS ONLY;

Space Consumed by Schema

Aggregate segment space by owner to produce a schema-level storage report, useful for capacity planning and chargeback:

SELECT
    s.owner,
    COUNT(*)                                        AS segment_count,
    ROUND(SUM(s.bytes) / 1073741824, 2)             AS total_gb,
    ROUND(SUM(CASE WHEN s.segment_type IN ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
                   THEN s.bytes ELSE 0 END) / 1073741824, 2) AS table_gb,
    ROUND(SUM(CASE WHEN s.segment_type IN ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
                   THEN s.bytes ELSE 0 END) / 1073741824, 2) AS index_gb,
    ROUND(SUM(CASE WHEN s.segment_type IN ('LOB','LOB PARTITION')
                   THEN s.bytes ELSE 0 END) / 1073741824, 2) AS lob_gb,
    COUNT(DISTINCT s.tablespace_name)               AS tablespace_count
FROM
    dba_segments s
GROUP BY
    s.owner
ORDER BY
    total_gb DESC;

Space Breakdown by Segment Type

Understand the overall storage profile of the database by segment type, helping identify whether indexes or LOBs are disproportionately large:

SELECT
    s.segment_type,
    COUNT(*)                                    AS segment_count,
    ROUND(SUM(s.bytes) / 1073741824, 2)         AS total_gb,
    ROUND(AVG(s.bytes) / 1048576, 2)            AS avg_mb,
    ROUND(MAX(s.bytes) / 1073741824, 3)         AS max_gb,
    ROUND(SUM(s.extents) / COUNT(*), 1)         AS avg_extents
FROM
    dba_segments s
GROUP BY
    s.segment_type
ORDER BY
    total_gb DESC;

Space by Schema and Tablespace

Break down storage consumption by both owner and tablespace — critical when multiple schemas share tablespaces and you need to attribute usage accurately:

SELECT
    s.owner,
    s.tablespace_name,
    COUNT(*)                                    AS segment_count,
    ROUND(SUM(s.bytes) / 1073741824, 2)         AS total_gb,
    ROUND(SUM(s.extents) / COUNT(*), 1)         AS avg_extents_per_seg
FROM
    dba_segments s
GROUP BY
    s.owner,
    s.tablespace_name
ORDER BY
    total_gb DESC;

High Extent Count Segments

Find segments with unusually high extent counts, which can indicate fragmentation or that NEXT_EXTENT is set too small:

SELECT
    s.owner,
    s.segment_name,
    s.partition_name,
    s.segment_type,
    s.tablespace_name,
    s.extents,
    ROUND(s.bytes / 1073741824, 3)              AS size_gb,
    ROUND(s.bytes / NULLIF(s.extents, 0) / 1048576, 2) AS avg_extent_mb,
    t.extent_management,
    t.allocation_type
FROM
    dba_segments    s
    JOIN dba_tablespaces t ON t.tablespace_name = s.tablespace_name
WHERE
    s.extents > 1000
ORDER BY
    s.extents DESC
FETCH FIRST 30 ROWS ONLY;

Common Use Cases

• Space reclamation — Identify the top tables and indexes by size, then check DBA_TABLES.NUM_ROWS vs segment size to find candidates for purge, compression, or rebuild
• Schema chargeback — Produce per-owner storage totals to allocate infrastructure costs across development teams or business units
• Tablespace capacity analysis — Aggregate BYTES by TABLESPACE_NAME to confirm how space is being consumed within a nearly full tablespace before adding datafiles
• LOB management — Filter SEGMENT_TYPE IN (‘LOB’,‘LOB PARTITION’) to find large LOB segments that may benefit from SECUREFILE conversion or archival
• Fragmentation detection — High EXTENTS counts on locally managed SYSTEM-allocation tablespaces may indicate objects that should be reorganised or moved to UNIFORM allocation tablespaces
• Pre-export sizing — Sum bytes for a given schema before running Data Pump export to estimate dump file size and verify target storage has sufficient headroom

Related Views

• DBA_TABLESPACES — Tablespace configuration including extent management type and ASSM settings that affect how segments grow
• DBA_DATA_FILES — Physical datafiles hosting these segments; join to understand which file a tablespace’s segments reside in
• DBA_FREE_SPACE — Free space available in each tablespace; compare with DBA_SEGMENTS totals to assess overall tablespace utilisation
• DBA_TEMP_FILES — Temp files used by sort and hash operations; temporary segments do not appear in DBA_SEGMENTS
• V$SORT_USAGE — Active temporary segment usage per session; complements DBA_SEGMENTS for understanding transient space consumption
• DBA_EXTENTS — The individual extents making up each segment; use when you need file_id and block_id level detail below the segment

Version Notes

• Oracle 8i: DBA_SEGMENTS has been available since Oracle 7; SEGMENT_TYPE values expanded with the introduction of IOTs and partitioned objects
• Oracle 9i: LOB and LOB PARTITION segment types added as LOB storage matured; LOBINDEX type introduced
• Oracle 10g: INITIAL_EXTENT and NEXT_EXTENT behaviour changed for locally managed tablespaces with SYSTEM allocation — NEXT_EXTENT may be NULL
• Oracle 11g: TABLE SUBPARTITION and INDEX SUBPARTITION types added; no new columns introduced to DBA_SEGMENTS itself
• Oracle 12c (Multitenant): In a CDB, DBA_SEGMENTS from the CDB root shows all PDB segments; each PDB sees only its own segments; CON_ID column added
• Oracle 19c: Deferred segment creation (introduced in 11g) means newly created empty tables may have no row in DBA_SEGMENTS until first insert
• Oracle 23ai: No structural changes; new JSON relational duality views create associated segments tracked here under standard segment types