Skip to Main Content

Allocated and wasted space in blob field

User_94JECJul 14 2022

I have a blob field in a table.
if I execute the dbms_lob.getlength function:
select sum(dbms_lob.getlength("CAMPO")) as bytes from TABLE;
I get this result:
85659787749 (about 79.7GB)

If I execute the dbms_space.space_usage procedure on the lob field I get:
immagine.pngimmagine.pngThere is a big difference between the Full Bytes in segment value and the result from the dbms_lob.getlength procedure. Why?

Type : LOB
Attr1 : USER
Attr2 : SYS_LOB0008853631C00007$$
Message : space saved 117145600 byte.
More info :
Allocated space: 115432488960
Utilized space: 115315343360
Reclaimed space :117145600

I would like to understand why there is such difference between the output from the dbms_space.space_usage procedure and the dbms_lob.getlength function.

This post has been answered by Jonathan Lewis on Jul 15 2022
Jump to Answer
Post Details
Added on Jul 14 2022