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