SQL & PL/SQL

Hello,

I try the LOB features, specially the SecureFile option in my database 11gR1.

In a PL/SQL procedure, I use one CLOB variable and put a lot of information in it (with loops). After treatment, I insert the CLOB variable in a table TEST with CLOB column.
There is only 1 row in my table !
All is fine.

The table TEST is created with securefile option. Here is the DDL:

create table sh1.test(id number,text clob)
LOB(text) STORE AS SECUREFILE(CACHE LOGGING);

Later, I check the space used by the LOB segment of my table and I'm very surprised.
I check also the length of my CLOB and I compare with the space used.... It seems to me that the space used is TO BIG for the length of the CLOB...

Commands I used for the space used and the length ! I
select bytes/1024/1024 from dba_segments where segment_name='SYS_LOB0000077271C00002$$';

select dbms_lob.getlength(text) from sch1.test;

Here are my measures results with length of the clob in comparison with the space it uses:

Length of the clob : 84306 748884 2234809 5291295 15211012

Space used [Mb] : 1.3125 74.3125 320.4375 1074.375 6924.4375 !!!!!!

What do you think of these results ?
Is it normal ? I hope not...

I tried to create the table in basicfile mode... The space used is not so different as securefile...

In my PL/SQL code, I tried different ways to append data to my CLOB.
- Concatenation ||
- dbms_lob.append
- dbms_lob.writeappend

There is no difference.

Any idea ?

Thanks a lot in advance,
Regards,

Yann

Edited by: 881275 on Oct 28, 2011 4:54 PM

Edited by: 881275 on Oct 28, 2011 4:55 PM