Question: I need to estimate the average length of
a BLOB column in my table. I Have found two ways to estimate a BLOB
- Join dba_segments into dba_lobs
using (owner, segment_name)
b.table_name = 'BLOB_TABLE';
Beware, there are issues where using dbms_lob.getlength does
not give the correct BLOB size.
IMPORTANT! Make sure that you use double
quotes when using dbms_lob.getlength or you might get a false response:
nvl((sum(dbms_lob.getlength("BLOB_TABLE"))),0) as bytes
83848300852 ~ 78.0898154266179 GB
61907953222 ~ 57.6562743838876 GB
Which one of these methods gives the correct BLOB length? How
can I check the length of a Long Datatype in a table column?
Answer: As you have demonstrated, there are many
ways to get the average length of a BLOB column. The best-practices
approach is to use the dbms_lob.getlength procedure.
Oracle has many
types of large objects (BLOB, CLOB, LONG, LONG RAW), and you need to run a
utility to find the actual used space in a long object (BLOB) data type within a
There are a few ways to compute the actual data length,
but I prefer the dbms_lob package:
dbms_lob.getlength(new_rec) desc ;
Here is a working example of computing the row length in a case where the
name and datatype of each row are known in advance:
"Total bytes per row"
case = 123;