Easily Query LOB Segment Size in Oracle Tables: Easy Guide

Managing LOB segment size in Oracle tables is crucial for Oracle DBAs to monitor the size of large object (LOB) data stored in tables.

The following SQL script will help you query the LOB segment size at the column level within a table, providing you with insights into how much space each LOB column consumes.

Oracle table LOB size for a particular table:

SET LINESIZE 250
COLUMN owner FORMAT A15
COLUMN table_name FORMAT A15
COLUMN column_name FORMAT A15
COLUMN segment_name FORMAT A25
COLUMN tablespace_name FORMAT A15
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT lb.owner,
               lb.table_name,
               lb.column_name,
               lb.segment_name,
               lb.tablespace_name,
               ROUND(seg.bytes/1024/1024,2) size_mb
        FROM   dba_lobs lb
               JOIN dba_segments seg ON seg.owner = lb.owner AND seg.segment_name = lb.segment_name
      AND lb.owner='&OWNER'
      AND lb.TABLE_NAME='&TABLE_NAME'
        ORDER BY 6 DESC)
WHERE  ROWNUM <= 20;

If you do not know the table name or if you want to find the details for all the tables under a schema, then you can run the below script:

Oracle LOB segment query for all tables:

SET LINESIZE 250
COLUMN owner FORMAT A15
COLUMN table_name FORMAT A15
COLUMN column_name FORMAT A15
COLUMN segment_name FORMAT A25
COLUMN tablespace_name FORMAT A20
COLUMN size_mb FORMAT 99999999.00
SELECT *
FROM   (SELECT lb.owner,
               lb.table_name,
               lb.column_name,
               lb.segment_name,
               lb.tablespace_name,
               ROUND(seg.bytes/1024/1024,2) size_mb
        FROM   dba_lobs lb
               JOIN dba_segments seg ON seg.owner = lb.owner AND seg.segment_name = lb.segment_name
      AND lb.owner='&OWNER'
        ORDER BY 6 DESC)
WHERE  ROWNUM <= 20;

Oracle Database LOB Documentation: Official Oracle documentation on LOB support and management.

If you want to check the table size in Oracle database, you can follow my article, Table Size in Oracle.