oracedba wrote:For what purpose are you trying to calculate that?
i am trying to calculate the avg size of a row, in which i have one column which has clob datatype,
oracedba wrote:Again, I'll ask... For what purpose?
I am trying to calculate the avg row length for a table,
Using length (which will give me number of characters, not number of bytes):
drop table t; create table t(clob_col) as select to_clob(rpad('é',2000,'é'))||rpad('a',4000,'a')||rpad('a',level*10,'a') from dual connect by level <= 400;
Real segment size (including space left over for LOB undo):
select round(sum(length(clob_col))/1024/1024, 3) "Length Meg" from t; Length Meg ---------- 3.054
variable table_name varchar2(30); exec :table_name := 'T'; select sum(bytes)/1024/1024 "Real Meg" from user_segments where segment_name in ( select val from ( select TABLE_NAME, SEGMENT_NAME, INDEX_NAME from user_lobs where TABLE_NAME = :table_name ) unpivot(val for col in(TABLE_NAME, SEGMENT_NAME, INDEX_NAME)) ); Real Meg ---------- 8.125
oracedba wrote:I agree with Blu - it is a pointless and meaningless exercise.
yes this is for volumetrics for future disk space,so that is the reason i am asking.