Hello,

We configured Table with BLOB Column with separate TableSpace.

CREATE TABLE EV_LOG
   (    "ID" FLOAT(63),
        "MESSAGE_ID" VARCHAR2(128 BYTE),
        "EVENT_TYPE" VARCHAR2(128 BYTE) CONSTRAINT "SYS_C0014415" NOT NULL ENABLE,
        "CLASSIFIER" VARCHAR2(128 BYTE),
        "EVENT_TIME" TIMESTAMP (6) CONSTRAINT "SYS_C0014416" NOT NULL ENABLE,
        "EVENT_DATA" BLOB
   )
     LOB ("EVENT_DATA") STORE AS "EV_LOG_LOB"( TABLESPACE dwh_data04 DISABLE STORAGE IN ROW )
     PARTITION BY RANGE ("EVENT_TIME")
     INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
     STORE IN (dwh_data03, dwh_data02)
  (
   PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('01-01-2012', 'DD-MM-YYYY')),
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-02-2012', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('01-03-2012', 'DD-MM-YYYY'))
  );

configured Scheduled Job to update BLOB Column to EMPTY, This Job runs every night and update 30 days old rows.

SET event_data = empty_blob()

Despite this we can see spike in tablespace usage, We have not 3 month data and Every day it require 2-3 GB More space for inserting 0.4 to 0.42 millions of rows every day.

  RUN_TIME   NAME            ALLOC_SIZE_GB CURR_USED_SIZE_GB PREV_USED_SIZE_GB VARIANCE
---------- --------------- ------------- ----------------- ----------------- --------------------
2017-03-29 dwh_data04           480            224.07            220.88 3.19
2017-03-30 dwh_data04           480            227.51            224.07 3.44
2017-03-31 dwh_data04           480            230.05            227.51 2.54
2017-04-01 dwh_data04           480            233.26            230.05 3.21
2017-04-02 dwh_data04           480            236.07            233.26 2.81
2017-04-03 dwh_data04           480             239.5            236.07 3.43
2017-04-04 dwh_data04           480            242.75             239.5 3.25
2017-04-05 dwh_data04           480            244.88            242.75 2.13

What could be the possible reason for space consumption, As per my understanding ORACLE should re-utilize empty space.

Regadrs,
Sachin