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;
