Hello,
We configured Table with BLOB Column with seperate 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'))
);
We 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 3month 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