we are facing an tablespace size growth issue due to BLOB and we resolve it with below question https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534388300346583586#9534627800346886091 thanks to Connor

As per the solution I am seeting PCTVERSION to 0 on Oracle 12cR1 but it fail.

Below is the SQL Code

CREATE SMALLFILE TABLESPACE lobs
DATAFILE '/u02/oradata/dwh/lobs_1.dbf' SIZE 100M AUTOEXTEND OFF ,
         '/u02/oradata/dwh/lobs_2.dbf' SIZE 100M AUTOEXTEND OFF   
LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 16K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

create table ev_log
      (    id float(63),
            event_time timestamp (6) constraint sys_c0014416 not null enable,
            event_data blob
       )
         lob (event_data) store as ev_log_lob( tablespace lobs disable storage in row )
       partition by range (event_time)
       interval(numtoyminterval(1, 'month'))
       store in (users)
     (
      partition pos_data_p1 values less than (to_date('01-01-2017', 'dd-mm-yyyy')),
      partition pos_data_p2 values less than (to_date('01-02-2017', 'dd-mm-yyyy')),
      partition pos_data_p3 values less than (to_date('01-03-2017', 'dd-mm-yyyy'))
     );

-- Data population code reapet 3-4 times

insert into ev_log
    select
      rownum,
      date '2017-02-28'-rownum/10,
      rpad('AB',4000,'AB')
    from dual
    connect by level <= 1000;
commit;

-- check tablespace size
select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';

-- Update BLOB to Empty
update ev_log set event_data = empty_blob();
commit;

I ran above data population code couple time to make sure tablespace size keep growing despite setting BLOB to empty.

we have a solution as below which was working find on 11gR2 and 12cR2 but fail on 12cR1

select column_name, nvl(retention, 0) retention, nvl(pctversion,0) pctversion from dba_lobs where table_name = 'EV_LOG';

alter table ev_log modify lob (event_data)  (pctversion 0); 

select column_name, nvl(retention, 0) retention, nvl(pctversion,0) pctversion from dba_lobs where table_name = 'EV_LOG';

COLUMN_NAME  RETENTION  PCTVERSION  
EVENT_DATA   0          10          

Table EV_LOG altered.

COLUMN_NAME  RETENTION  PCTVERSION  
EVENT_DATA   0          0  

Hello,

I have executed sample code it gives me weired results, not sure why and how.

-- CLOB
SQL> create table t ( x clob) ;

Table created.

SQL> select column_name, nvl(retention, 0) retention, nvl(pctversion,0) pctversion from   dba_lobs where table_name = 'T';

COLUMN_NAME           RETENTION PCTVERSION
-------------------- ---------- ----------
X                             0          0

SQL> alter table t modify lob (x) (pctversion 0);

Table altered.

SQL> select column_name, nvl(retention, 0) retention, nvl(pctversion,0) pctversion from   dba_lobs where table_name = 'T';

COLUMN_NAME           RETENTION PCTVERSION
-------------------- ---------- ----------
X                             0         10


-- BLOB

SQL> drop table t;

Table dropped.

SQL> create table t (  x blob);

Table created.

SQL> select column_name, nvl(retention, 0) retention, nvl(pctversion,0) pctversion from   dba_lobs where table_name = 'T';

COLUMN_NAME           RETENTION PCTVERSION
-------------------- ---------- ----------
X                             0          0

SQL> alter table t modify lob (x) (pctversion 0);

Table altered.

SQL>  select column_name, nvl(retention, 0) retention, nvl(pctversion,0) pctversion from   dba_lobs where table_name = 'T';

COLUMN_NAME           RETENTION PCTVERSION
-------------------- ---------- ----------
X                             0         10