Oracle 12c extended VARCHAR - LOB segment grows indefinitely

we have an application that was migrated to Oracle 12 and we make use of the new extended VARCHAR feature[1].

One of our tables undergoes a flush and fill procedure on a regular basis where the delete and insert is done in one transaction.

However it seems that when doing this, it seems that Oracle doesn't cleanup old data from the LOB segment that is associated with an "extended VARCHAR" column.

The following script can be used to reproduce this problem:

Table setup:

   create table blob_test (id integer primary key, content varchar2(32000));

To simulate our daily flush and fill process, run the following script:

    begin
      for i in 1..10 loop

        delete from blob_test;

        insert into blob_test (id, content)
        select level, rpad('*', dbms_random.value(50,30000))
        from dual
        connect by level <= 10000;

        commit;

      end loop;

Note that in real life, the transaction doing this is much bigger (it involves several tables) It also doesn't matter how much time lies between the individual transactions, this typically happens once a day, and during that time the table is only read, never written.

To check the allocated space vs. the actual space I use the following statement:

    select 'real size' as what,

           sum(vsize(content)) / 1024 / 1024 as size_mb     from blob_test
    union all
    select 'size on disk',

           sum(bytes) / 1024 / 1024
    from user_segments
    where segment_name = (select segment_name from user_lobs where table_Name = 'BLOB_TEST');

and this is what I get:

    WHAT | SIZE_MB
    -------------+--------
    real size | 141.77
    size on disk | 1984.19

So the segment grew to nearl 2GB even though the actual space needed is less then 150MB.

When committing the delete _before_ running the insert, the space is properly released by Oracle. But as the table is used (queried) during this process, we cannot do that in two separate transactions.

Oracle won't let me alter the options for the implicit LOB segment associated with an extended VARCHAR column, so I can't change e.g. the retention property.

The problem is, that the tablespace where the data ist stored is growing and growing.

The only solution we found so far, was to take the application offline, then do the delete all data and commit that right away, then re-insert the data.

But this is only a temporary workaround. If we can't fix this behaviour we will have to use a CLOB column instead, which we would like to avoid because the read performance of an extended VARCHAR is indeed much better than for a CLOB column.

Any other ideas?

Thomas

[1] http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#FEATURENO09739

--
http://www.freelists.org/webpage/oracle-l