How to find the actual size of a table containing LOB columns

The actual size of a table containing LOB segments cannot be calculated from dba_extents  table .

Use the below sql to find the lob columns in a table

col COLUMN_NAME for a30

select SEGMENT_NAME,TABLE_NAME,COLUMN_NAME from dba_lobs where TABLE_NAME='SUNIL_DATA_TMP';

SEGMENT_NAME                   TABLE_NAME                     COLUMN_NAME

------------------------------ ------------------------------ --------------------

SYS_LOB0018438706C00018$$      SUNIL_DATA_TMP            PAYLOAD

SYS_LOB0018438706C00019$$      SUNIL_DATA_TMP            NOTIFICATIONDATA

SYS_LOB0018438706C00021$$      SUNIL_DATA_TMP            ADDITIONALCOMMPROTOCOLVALUE

SQL> select sum(bytes)/1024/1024/1024 SIZE_GB  from dba_extents where segment_name='SYS_LOB0018438706C00018$$';

SIZE_GB

--------------------

309

SQL> select sum(bytes)/1024/1024/1024 SIZE_GB  from dba_extents where segment_name='SYS_LOB0018438706C00019$$';

SIZE_GB

--------------------

120

SQL> select sum(bytes)/1024/1024/1024 SIZE_GB from dba_extents where segment_name='SYS_LOB0018438706C00021$$';

SIZE_GB

--------------------

.125

Either WE can Get Size of Each LOB size from Above or Like in below with Single Step by using dbms_lob.getlength

VERY IMPORTANT!  Make sure that you use double quotes when using dbms_lob.getlength or you might get a false response:

select 

   nvl((sum(dbms_lob.getlength("LOB_TABLE"))),0) as bytes 

from 

   blob_table;

bytes

----------------

68007953222 ~ 60.6562743838876 GB