How to find BLOB/CLOB columns size in Oracle tables

Here I'll show you how to calculate blob, clob columns sizes in tables.
1) Find the size of individual rows with the CLOB/BLOB column. DBMS_LOB.GETLENGTH returns the number of characters (bytes) in the CLOB/BLOB column.

SELECT DBMS_LOB.GetLength("BLOB_COLUMN") / 1024 / 1024 AS SizeMB
  FROM SCHEMA_NAME.TABLE_NAME_WITH_BLOB;

2) find the size of all rows with BLOB/CLOB for particular tables

SELECT SUM(DBMS_LOB.GetLength("BLOB_COLUMN")) / 1024 / 1024 AS SizeMB
  FROM SCHEMA_NAME.TABLE_NAME_WITH_BLOB

3) If you want to find all BLOB/CLOB sizes in your oracle database for a particular schema here is code:

DECLARE
    v_Size NUMBER := 0;
    v_TotalSize NUMBER := 0;
BEGIN
    FOR v_Rec IN (
                  SELECT OWNER || '.' || TABLE_NAME || '.' || COLUMN_NAME AS TableAndColumn,
                     'SELECT SUM(DBMS_LOB.GetLength("' || COLUMN_NAME || '"))/1024/1024 AS SizeMB FROM ' || OWNER || '.' || TABLE_NAME AS sqlstmt
                  FROM DBA_TAB_COLUMNS
                  WHERE DATA_TYPE LIKE '_LOB'
                        AND OWNER LIKE '%SCHEMA_NAME%' -- SCHEMA_NAME
                        AND TABLE_NAME NOT LIKE 'BIN$%' -- EXCLUDE DELETED TABLES FROM RECYCLE_BIN
                 )
    LOOP
        EXECUTE IMMEDIATE v_Rec.sqlstmt INTO v_Size;
 
        DBMS_OUTPUT.PUT_LINE (v_Rec.TableAndColumn || ' size in MB is ' || ROUND(NVL(v_Size,0),2));
        v_TotalSize := v_TotalSize + NVL(v_Size,0);
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE ('Total size in MB is ' || ROUND(v_TotalSize,2));
END;