Question: I need to estimate the average length of
a BLOB column in my table. I Have found two ways to estimate a BLOB
length:
- Join dba_segments into dba_lobs
- dbms_lob.getlength
select
table_name,
column_name,
segment_name,
a.bytes
from
dba_segments a
join
dba_lobs b
using (owner, segment_name)
where
b.table_name = 'BLOB_TABLE';
ETM_RAW_XML
IFD_XML
SYS_LOB0007260522C00012$$ 87870668800
ETM_RAW_XML
ETM_XML
SYS_LOB0007260522C00011$$ 125199974400
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:
select
nvl((sum(dbms_lob.getlength("BLOB_TABLE"))),0) as bytes
from
blob_table;
bytes
----------------
83848300852 ~ 78.0898154266179 GB
select
nvl((sum(dbms_lob.getlength("BLOB_TABLE"))),0)
as bytes
from
blob_table;
bytes
----------------
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
table.
There are a few ways to compute the actual data length,
but I prefer the dbms_lob package:
select
dbms_lob.getlength(new_rec)
from
blob_mytab
order by
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:
select
nvl(dbms_lob.getlength(CASE_DATA),0)+1 +
nvl(vsize(CASE_NUMBER
),0)+1 +
nvl(vsize(CASE_DATA_NAME),0)+1 +
nvl(vsize(LASTMOD_TIME_T),0)+1
"Total bytes per row"
from
mytab
where
case = 123;
