How to list table sizes and their LOB segment sizes

Sometimes a quick sum of the used bytes in DBA_SEGMENTS is not enough to find the total size of a table.
If you have Large Object type columns defined in the table, the total table size can potentially be far greater than what you'd think.

Let's look at an example.

I have two tables in two differen schemas, with the same name. The tables are identical, and they both contain a LOB column:

SQL> desc livedocs.documents
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(19)
 VERSION                                            NUMBER(19)
 DOC_ID                                             NOT NULL NUMBER(19)
 TYPE                                               NOT NULL VARCHAR2(255 CHAR)
 NUM_PAGES                                          NUMBER(15)
 CREATED                                            TIMESTAMP(6)
 LAST_CHANGED                                       TIMESTAMP(6)
 CONTENTS                                           NOT NULL BLOB

Check the tables' sizes:

SELECT OWNER,SEGMENT_NAME,SUM(BYTES)/1024/1024/1024 "table size (GB)" 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME IN ('DOCUMENTS') 
AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS')
GROUP BY OWNER,SEGMENT_NAME;

Result:

OWNER	SEGMENT_NAME	table size (GB)
LIVEDOCS	DOCUMENTS	2,5
ARCHIVEDDOCS	DOCUMENTS	1,875

Let's find the name of the LOB segments and their corresponding indexes:
(The LOB indexes are internal data structures only. See this post.)

SELECT OWNER,TABLE_NAME,SEGMENT_NAME,COLUMN_NAME,INDEX_NAME 
FROM DBA_LOBS 
WHERE TABLE_NAME='DOCUMENTS' 
AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS');

OWNER	TABLE_NAME	SEGMENT_NAME	COLUMN_NAME	INDEX_NAME
ARCHIVEDDOCS	DOCUMENTS	ARCHIVED_DOCS_LOB	CONTENT	SYS_IL0000123181C00008$$
LIVEDOCS	DOCUMENTS	LIVE_DOCS_LOB	CONTENT	SYS_IL0000194213C00009$$

How much space have been allocated to these LOB segments?

SELECT OWNER,SEGMENT_NAME,ROUND(SUM(BYTES)/1024/1024/1024) "LOB size (GB)" 
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN 
    (
    SELECT SEGMENT_NAME 
    FROM DBA_LOBS WHERE TABLE_NAME='DOCUMENTS'
    AND OWNER IN ('LIVEDOCS','ARCHIVEDDOCS')
    )
GROUP BY OWNER,SEGMENT_NAME 
; 

OWNER	SEGMENT_NAME	LOB size (GB)
LIVEDOCS	LIVE_DOCS_LOB	1256
ARCHIVEDDOCS	ARCHIVED_DOCS_LOB	3642

Putting it together, you can list both the table and its dependent LOB segments like this:

SELECT S.OWNER "Owner",NVL(S.SEGMENT_NAME, 'TABLE TOTAL SIZE') "Segment name",ROUND(SUM(S.BYTES)/1024/1024/1024,1) "Segment size (GB)"
FROM  DBA_SEGMENTS S 
WHERE S.SEGMENT_NAME IN ('DOCUMENTS')
AND   S.OWNER IN ('LIVEDOCS','ARCHIVEDOCS')
OR    S.SEGMENT_NAME IN (
                    (
                        SELECT L.SEGMENT_NAME FROM DBA_LOBS L 
                        WHERE L.TABLE_NAME = 'DOCUMENTS' 
                        AND L.OWNER = S.OWNER 
                        AND L.OWNER in ('LIVEDOCS','ARCHIVEDOCS')
                    )
)
GROUP BY S.OWNER,ROLLUP(S.SEGMENT_NAME)
ORDER BY 1,2,3; 

The resulting listing is:

Owner	Segment name	Segment size (GB)
ARCHIVEDDOCS	ARCHIVED_DOCS_LOB	3642,4
ARCHIVEDDOCS	DOCUMENTS	1,9
ARCHIVEDDOCS	TABLE TOTAL SIZE	3644,3
LIVEDOCS	DOCUMENTS	2,5
LIVEDOCS	LIVE_DOCS_LOB	1255,9
LIVEDOCS	TABLE TOTAL SIZE	1258,4

Addendum, 2025-02-06:

With assistance from Chat GPT, I realized that a better query would be to use named subqueries, or CTEs (Common Table Expressions). They can be built using the WITH-clause in SQL, and referred to in the main query:

set lines 200
col OWNER format a30
col TABLE_NAME format a30
col TABLE_SIZE_GB format 99
col LOB_SEGMENT_NAME format a30
col LOB_COLUMN_NAME format a30
col LOB_DATA_TYPE format a30
col LOB_SIZE_GB format 999 
BREAK ON OWNER
WITH
    -- CTE to get the tables with LOB objects
    lob_tables AS (
        SELECT DISTINCT TABLE_NAME, OWNER
        FROM DBA_TAB_COLUMNS
        WHERE OWNER = 'SCOTT'
          AND DATA_TYPE IN ('NCLOB', 'BLOB', 'RAW'  )
          AND TABLE_NAME NOT LIKE 'BIN$%'
    ),
    -- CTE to get LOB segment names with their corresponding table and column names
    lob_segments AS (
        SELECT DISTINCT L.OWNER,
                        L.TABLE_NAME,
                        L.SEGMENT_NAME AS LOB_SEGMENT_NAME,
                        C.COLUMN_NAME AS LOB_COLUMN_NAME,
                        C.DATA_TYPE AS LOB_DATA_TYPE
        FROM DBA_LOBS L
        JOIN DBA_TAB_COLUMNS C
          ON L.TABLE_NAME = C.TABLE_NAME
         AND L.COLUMN_NAME = C.COLUMN_NAME
        WHERE L.OWNER = 'SCOTT'
    ),
    -- CTE to get the table size in GB
    table_sizes AS (
        SELECT S.OWNER,
               S.SEGMENT_NAME AS TABLE_NAME,
               ROUND(SUM(S.BYTES)/1024/1024/1024, 1) AS TABLE_SIZE_GB
        FROM DBA_SEGMENTS S
        WHERE S.OWNER = 'SCOTT'
          AND EXISTS (SELECT 1 FROM lob_tables LT WHERE LT.TABLE_NAME = S.SEGMENT_NAME)
        GROUP BY S.OWNER, S.SEGMENT_NAME
    ),
    -- CTE to get the LOB size in GB
    lob_sizes AS (
        SELECT L.OWNER,
               L.TABLE_NAME,
               L.SEGMENT_NAME AS LOB_SEGMENT_NAME,
               ROUND(SUM(S.BYTES)/1024/1024/1024, 1) AS LOB_SIZE_GB
        FROM DBA_LOBS L
        JOIN DBA_SEGMENTS S
          ON L.SEGMENT_NAME = S.SEGMENT_NAME
         AND L.OWNER = S.OWNER
        WHERE L.OWNER = 'SCOTT'
        GROUP BY L.OWNER, L.TABLE_NAME, L.SEGMENT_NAME
    )
-- Final query to combine everything
SELECT TS.OWNER,
       TS.TABLE_NAME,
       TS.TABLE_SIZE_GB,
       LS.LOB_SEGMENT_NAME,
       LS.LOB_COLUMN_NAME,
       LS.LOB_DATA_TYPE,
       LZ.LOB_SIZE_GB
FROM table_sizes TS
JOIN lob_segments LS
  ON TS.OWNER = LS.OWNER
 AND TS.TABLE_NAME = LS.TABLE_NAME
JOIN lob_sizes LZ
  ON LS.OWNER = LZ.OWNER
 AND LS.LOB_SEGMENT_NAME = LZ.LOB_SEGMENT_NAME
ORDER BY TS.OWNER, TS.TABLE_NAME, LS.LOB_SEGMENT_NAME;

This gives you a practical row-by-row view of everything you need to know about the LOB segments:

OWNER	TABLE_NAME	TABLE_SIZE_GB	LOB_SEGMENT_NAME	LOB_COLUMN_NAME	LOB_DATA_TYPE	LOB_SIZE_GB
SCOTT	TABLE1	0	SYS_LOB0003064134C00015$$	MYCOL1	NCLOB	0
	TABLE1	0	SYS_LOB0003064134C00028$$	MYCOL2	NCLOB	0
	TABLE2	0,2	SYS_LOB0003064148C00005$$	MYCOL1	NCLOB	1
	TABLE2	0,2	SYS_LOB0003064148C00017$$	MYCOL2BLOB	BLOB	0
	TABLE3	0,2	SYS_LOB0003064159C00004$$	MYCOL1	NCLOB	237
	TABLE3	0,1	SYS_LOB0003064143C00003$$	MYCOL1	NCLOB	0