Query the size of a table with a lob field in the Oracle database
Note: Because the lob field has an independent lob segment for storage, for a table with a lob field, you cannot only query dba_segments.
The following scripts are from:
How to Compute the Size of a Table containing Outline CLOBs and BLOBs [Article ID 118531.1]
Modified to NVL (SUM (S. BYTES), 0)
SQL> col "TOTAL TABLE SIZE" format 99999999999999
--- Note: The following SQL statements can be executed directly. For the convenience of copying the script, the SQL statement at execution is placed instead of the log of the SQL statement. SELECT (select nvl (SUM (S. BYTES), 0) -- The Table Segment size FROM DBA_SEGMENTS s where s. OWNER = UPPER ('lc001129 ') AND (S. SEGMENT_NAME = UPPER ('zwpzk') + (select nvl (SUM (S. BYTES), 0) -- The Lob Segment Size FROM DBA_SEGMENTS S, DBA_LOBS l where s. OWNER = UPPER ('lc0020179') AND (L. SEGMENT_NAME = S. SEGMENT_NAME and l. TABLE_NAME = UPPER ('zwpzk') and l. OWNER = UPPER ('lc0020179') + (select nvl (SUM (S. BYTES), 0) -- The Lob Index size FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S. OWNER = UPPER ('lc0020179') AND (I. INDEX_NAME = S. SEGMENT_NAME and I. TABLE_NAME = UPPER ('zwpzk') AND INDEX_TYPE = 'lob' and I. OWNER = UPPER ('lc0020179') "total table size" from dual; total table size -------------- 3571869286SQL> select 3571869286/1024/1024/1024 from dual; 3571869286/1024/1024/1024 ------------------------ 3.3265625