In Oracle database (starting from 10g), a table’s real storage size is not limited to its table segment. To get an accurate size, you must include:

• Table segment
• Standard indexes
• LOB segments (BLOB/CLOB/NCLOB)
• LOB indexes

Ignoring these leads to major underestimation, especially for large or document-heavy tables.

Why it matters

A simple DBA_SEGMENTS query only shows the table size, not its full footprint.

Example:

• Table: 10 GB
• Indexes: 5 GB
• LOBs: 120 GB
• LOB indexes: 1 GB

➡ Real size = 136 GB, not 10 GB

Complete query

DEFINE OWNER = 'WADHAH_OWN'
DEFINE TABLE_NAME='TN_DATA_HIST'

SELECT segment_type,
       segment_name,
       ROUND(bytes/1024/1024,2) size_mb
FROM (
    /* Table */
    SELECT s.segment_type,
           s.segment_name,
           s.bytes
    FROM dba_segments s
    WHERE s.owner = UPPER('&OWNER')
      AND s.segment_name = UPPER('&TABLE_NAME')

    UNION ALL

    /* Normal indexes */
    SELECT s.segment_type,
           s.segment_name,
           s.bytes
    FROM dba_indexes i
    JOIN dba_segments s
      ON s.owner = i.owner
     AND s.segment_name = i.index_name
    WHERE i.table_owner = UPPER('&OWNER')
      AND i.table_name = UPPER('&TABLE_NAME')

    UNION ALL

    /* LOB segments and indexes */
    SELECT s.segment_type,
           s.segment_name,
           s.bytes
    FROM dba_lobs l
    JOIN dba_segments s
      ON s.owner = l.owner
     AND (s.segment_name = l.segment_name
          OR s.segment_name = l.index_name)
    WHERE l.owner = UPPER('&OWNER')
      AND l.table_name = UPPER('&TABLE_NAME')
)
ORDER BY bytes DESC;

Then;

Real table size = Table + Indexes + LOB Segments + LOB Indexes