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

Finding the size of tables in Oracle can be a bit more nuanced than in other databases because Oracle manages space through segments.

To get an accurate picture, you generally want to look at the USER_SEGMENTS view (for your own schema) or DBA_SEGMENTS (if you have administrative privileges and want to see someone else’s schema).

Key Details to Keep in Mind

• USER_SEGMENTS vs DBA_SEGMENTS: Use USER_SEGMENTS to see tables you own. If you need to check a specific schema (e.g., ‘SALES_DATA’), use DBA_SEGMENTS and add WHERE owner = 'SALES_DATA'.
• Indexes: The query above only shows the table data itself. In Oracle, indexes are separate segments. If you want to see the total footprint of a table including its indexes, you would need to join user_segments with user_indexes.
• LOB Data: If your table has “Large Objects” (CLOBs or BLOBs), that data is often stored in a separate LOBSEGMENT. The query above won’t automatically group those with the table name unless you map them via the user_lobs view.

In conclusion a relevant query must include the size of associated indexes and LOB segments for a “total package” view of each table.

To get the “total package” size, we have to look across several different views. A single table in Oracle is often supported by Indexes and LOBs (Large Objects like CLOBs/BLOBs), which are stored in their own separate segments.

The following query uses a WITH clause to aggregate all these different pieces so you can see exactly how much disk space a table is “actually” responsible for.

The “Total Footprint” Query

To get the “total package” size, we have to look across several different views. A single table in Oracle is often supported by Indexes and LOBs (Large Objects like CLOBs/BLOBs), which are stored in their own separate segments.

The following query uses a WITH clause to aggregate all these different pieces so you can see exactly how much disk space a table is “actually” responsible for.

SELECT 
    t.table_name,
    ROUND(t.table_size_mb, 2) AS table_only_mb,
    ROUND(NVL(i.index_size_mb, 0), 2) AS indexes_mb,
    ROUND(NVL(l.lob_size_mb, 0), 2) AS lobs_mb,
    ROUND(t.table_size_mb + NVL(i.index_size_mb, 0) + NVL(l.lob_size_mb, 0), 2) AS total_combined_mb
FROM (
    -- Table segment size
    SELECT segment_name AS table_name, SUM(bytes)/1024/1024 AS table_size_mb
    FROM user_segments
    WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
    GROUP BY segment_name
) t
LEFT JOIN (
    -- Index segment size associated with tables
    SELECT i.table_name, SUM(s.bytes)/1024/1024 AS index_size_mb
    FROM user_indexes i
    JOIN user_segments s ON i.index_name = s.segment_name
    GROUP BY i.table_name
) i ON t.table_name = i.table_name
LEFT JOIN (
    -- LOB segment size associated with tables
    SELECT l.table_name, SUM(s.bytes)/1024/1024 AS lob_size_mb
    FROM user_lobs l
    JOIN user_segments s ON l.segment_name = s.segment_name
    GROUP BY l.table_name
) l ON t.table_name = l.table_name
ORDER BY total_combined_mb DESC;

In Oracle’s architecture, space is managed in a hierarchy. When you ask for the size of a table, you are often only seeing the “Top Level” segment.

• Table Segment: The actual rows of data.
• Index Segments: The B-Tree structures used for searching. These can sometimes be larger than the table itself if there are many columns indexed.
• LOB Segments: If you have a DESCRIPTION column that is a CLOB, Oracle stores the bulk of that text outside the table to keep the table rows lean.

---

Keep in mind that USER_SEGMENTS shows allocated space. If you delete 90% of your data, the total_combined_mb will stay the same.