Determine the size of all the tables in an Oracle database schema

By | November 11, 2026

Finding the true size of tables in Oracle Database can be significantly more nuanced than in other relational database systems. Unlike PostgreSQL (with pg_total_relation_size) or MySQL, Oracle manages physical storage through a hierarchy of blocks, extents, and segments spread across tablespaces.

If you simply query USER_TABLES or sum USER_SEGMENTS for segment_type = 'TABLE', you are only looking at the tip of the iceberg. A table’s real storage footprint is often dominated by its supporting structures: B-Tree indexes, LOB segments (for CLOB and BLOB columns), and LOB indexes.

In this guide, we will break down:

  1. A quick query to check overall schema space consumption.
  2. The complete “True Footprint” CTE query for your own schema (USER_ views).
  3. The DBA version to inspect any schema across the database (DBA_ views).
  4. How to detect and reclaim fragmented space trapped below the High-Water Mark (HWM).

1. Quick Check: Total Schema Size by Segment Type

Before diving into individual tables, it is often helpful to see how storage is divided across your schema at a high level:

SELECT 
    segment_type,
    COUNT(*) AS total_segments,
    ROUND(SUM(bytes) / 1024 / 1024, 2) AS total_size_mb,
    ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS total_size_gb
FROM user_segments
GROUP BY segment_type
ORDER BY total_size_mb DESC;

This immediately tells you whether your storage bottleneck is caused by base table rows, index overhead, or large object storage.

2. The Anatomy of an Oracle Table’s Footprint

When calculating a table’s disk usage, four distinct components must be aggregated:

  • Table Segments (TABLE, TABLE PARTITION, TABLE SUBPARTITION): The actual data blocks storing row records. For Index-Organized Tables (IOTs), the base data resides in an INDEX segment instead.
  • Index Segments (INDEX, INDEX PARTITION, INDEX SUBPARTITION): B-Tree search structures. On heavily indexed transaction tables, total index size frequently exceeds the table data itself.
  • LOB Segments (LOBSEGMENT, LOB PARTITION, LOB SUBPARTITION): Out-of-line storage for CLOB, BLOB, JSON, or XMLType data exceeding the 4,000-byte inline threshold.
  • LOB Indexes (LOBINDEX): Internal B-Tree structures Oracle maintains to navigate chunked LOB data. Most standard queries miss this segment completely, leaving tens of gigabytes unaccounted for.

3. The “True Footprint” Query (Single Schema)

The following query uses Common Table Expressions (CTEs via WITH) to pull data from USER_SEGMENTS, USER_INDEXES, and USER_LOBS. It maps base tables, secondary indexes, LOB data, and LOB index pointers into a unified, accurate disk footprint:

WITH 
table_segs AS (
    SELECT 
        segment_name AS table_name,
        SUM(bytes) / 1024 / 1024 AS table_mb
    FROM user_segments
    WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
    GROUP BY segment_name
),
index_segs AS (
    SELECT 
        i.table_name,
        SUM(s.bytes) / 1024 / 1024 AS index_mb
    FROM user_indexes i
    JOIN user_segments s ON i.index_name = s.segment_name
    WHERE s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
    GROUP BY i.table_name
),
lob_segs AS (
    SELECT 
        l.table_name,
        SUM(s.bytes) / 1024 / 1024 AS lob_mb
    FROM user_lobs l
    JOIN user_segments s ON s.segment_name IN (l.segment_name, l.index_name)
    WHERE s.segment_type IN ('LOBSEGMENT', 'LOBINDEX', 'LOB PARTITION', 'LOB SUBPARTITION')
    GROUP BY l.table_name
),
all_tables AS (
    SELECT table_name FROM table_segs
    UNION
    SELECT table_name FROM index_segs
    UNION
    SELECT table_name FROM lob_segs
)
SELECT 
    a.table_name,
    ROUND(NVL(t.table_mb, 0), 2) AS table_mb,
    ROUND(NVL(i.index_mb, 0), 2) AS index_mb,
    ROUND(NVL(l.lob_mb, 0), 2)   AS lob_mb,
    ROUND(NVL(t.table_mb, 0) + NVL(i.index_mb, 0) + NVL(l.lob_mb, 0), 2) AS total_mb,
    ROUND((NVL(t.table_mb, 0) + NVL(i.index_mb, 0) + NVL(l.lob_mb, 0)) / 1024, 2) AS total_gb
FROM all_tables a
LEFT JOIN table_segs t ON a.table_name = t.table_name
LEFT JOIN index_segs i ON a.table_name = i.table_name
LEFT JOIN lob_segs   l ON a.table_name = l.table_name
ORDER BY total_mb DESC;

Why this query is superior:

  1. Counts LOBINDEX: By checking s.segment_name IN (l.segment_name, l.index_name), LOB index pointers are fully accounted for.
  2. Handles Partitions: Incorporates TABLE PARTITION, TABLE SUBPARTITION, and partition variants for indexes and LOBs.
  3. Outer Union (all_tables): Ensures Index-Organized Tables (IOTs) or tables with only LOB allocations appear in the report even if they lack standard heap segments.

4. The DBA Cross-Schema Query (DBA_ Views)

If you have administrative privileges (such as SELECT_CATALOG_ROLE or DBA) and need to inspect another schema, use the DBA_ dictionary views:

WITH 
target_schema AS (
    SELECT UPPER('SALES_DATA') AS owner_name FROM dual
),
table_segs AS (
    SELECT 
        s.segment_name AS table_name,
        SUM(s.bytes) / 1024 / 1024 AS table_mb
    FROM dba_segments s
    JOIN target_schema ts ON s.owner = ts.owner_name
    WHERE s.segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
    GROUP BY s.segment_name
),
index_segs AS (
    SELECT 
        i.table_name,
        SUM(s.bytes) / 1024 / 1024 AS index_mb
    FROM dba_indexes i
    JOIN target_schema ts ON i.owner = ts.owner_name
    JOIN dba_segments s ON i.owner = s.owner AND i.index_name = s.segment_name
    WHERE s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
    GROUP BY i.table_name
),
lob_segs AS (
    SELECT 
        l.table_name,
        SUM(s.bytes) / 1024 / 1024 AS lob_mb
    FROM dba_lobs l
    JOIN target_schema ts ON l.owner = ts.owner_name
    JOIN dba_segments s ON l.owner = s.owner AND s.segment_name IN (l.segment_name, l.index_name)
    WHERE s.segment_type IN ('LOBSEGMENT', 'LOBINDEX', 'LOB PARTITION', 'LOB SUBPARTITION')
    GROUP BY l.table_name
),
all_tables AS (
    SELECT table_name FROM table_segs
    UNION
    SELECT table_name FROM index_segs
    UNION
    SELECT table_name FROM lob_segs
)
SELECT 
    ts.owner_name AS schema_owner,
    a.table_name,
    ROUND(NVL(t.table_mb, 0), 2) AS table_mb,
    ROUND(NVL(i.index_mb, 0), 2) AS index_mb,
    ROUND(NVL(l.lob_mb, 0), 2)   AS lob_mb,
    ROUND(NVL(t.table_mb, 0) + NVL(i.index_mb, 0) + NVL(l.lob_mb, 0), 2) AS total_mb,
    ROUND((NVL(t.table_mb, 0) + NVL(i.index_mb, 0) + NVL(l.lob_mb, 0)) / 1024, 2) AS total_gb
FROM all_tables a
CROSS JOIN target_schema ts
LEFT JOIN table_segs t ON a.table_name = t.table_name
LEFT JOIN index_segs i ON a.table_name = i.table_name
LEFT JOIN lob_segs   l ON a.table_name = l.table_name
ORDER BY total_mb DESC;

Simply change 'SALES_DATA' to the target schema you are auditing.

5. The Trap: Allocated Space vs. Actual Data (High-Water Mark)

A common point of confusion is running DELETE FROM my_table; and noticing that the query above still reports the exact same size.

Why?

USER_SEGMENTS and DBA_SEGMENTS measure allocated space on the filesystem, not live row data. In Oracle, space is assigned in chunks called extents. When you insert data, Oracle advances the High-Water Mark (HWM). When you run DELETE, Oracle marks rows as deleted inside the data blocks, but it does not move the HWM backward or deallocate extents back to the tablespace.

How to Estimate Actual Data vs. Allocated Space:

Make sure your optimizer statistics are fresh, then compare the allocated segment size with num_rows * avg_row_len:

-- 1. Gather fresh stats
EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'CUSTOMERS');

-- 2. Compare Allocated vs. Actual Used
SELECT 
    t.table_name,
    s.allocated_mb,
    ROUND((t.num_rows * t.avg_row_len) / 1024 / 1024, 2) AS actual_data_mb,
    ROUND(s.allocated_mb - ((t.num_rows * t.avg_row_len) / 1024 / 1024), 2) AS wasted_space_mb
FROM user_tables t
JOIN (
    SELECT segment_name, ROUND(SUM(bytes) / 1024 / 1024, 2) AS allocated_mb
    FROM user_segments
    WHERE segment_type IN ('TABLE', 'TABLE PARTITION')
    GROUP BY segment_name
) s ON t.table_name = s.segment_name
WHERE t.table_name = 'CUSTOMERS';

How to Reclaim Fragmented Space:

If a table has significant wasted space below the High-Water Mark, you can reclaim it using one of two methods:

Option A: Online Segment Shrink (Assumes ASSM Tablespace)

-- 1. Enable row movement
ALTER TABLE customers ENABLE ROW MOVEMENT;

-- 2. Compact rows and reset the High-Water Mark (releases space to tablespace)
ALTER TABLE customers SHRINK SPACE;

-- 3. Disable row movement (optional, best practice)
ALTER TABLE customers DISABLE ROW MOVEMENT;

Option B: Move Table & Rebuild Indexes

-- 1. Move table to compress out empty blocks
ALTER TABLE customers MOVE;

-- 2. Rebuild all indexes (moving marks existing indexes UNUSABLE)
ALTER INDEX idx_customers_email REBUILD;

Summary Cheat Sheet

ViewPurposeWhen to Use
USER_SEGMENTSAllocated physical disk blocksFinding true disk footprint for owned tables
DBA_SEGMENTSSystem-wide allocated extentsAuditing third-party or application schemas
USER_LOBSMaps LOB columns to LOBSEGMENT & LOBINDEXEssential for calculating CLOB/BLOB footprint
USER_TABLESLogical metadata (NUM_ROWS, AVG_ROW_LEN)Comparing actual row payload vs. allocated extents

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.