Hello Tom,
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
We hang on to "older" lob data in order to provide read consistency. You have control over that with PCTVERSION and RETENTION settings. For example
SQL> create tablespace LOBS datafile 'C:\ORACLE\ORADATA\DB11\LOBS.DBF' size 50m autoextend on next 10m;
Tablespace created.
SQL> create table ev_log
2 ( id float(63),
3 event_time timestamp (6) constraint sys_c0014416 not null enable,
4 event_data blob
5 )
6 lob (event_data) store as ev_log_lob( tablespace lobs disable storage in row )
7 partition by range (event_time)
8 interval(numtoyminterval(1, 'month'))
9 store in (users)
10 (
11 partition pos_data_p1 values less than (to_date('01-01-2017', 'dd-mm-yyyy')),
12 partition pos_data_p2 values less than (to_date('01-02-2017', 'dd-mm-yyyy')),
13 partition pos_data_p3 values less than (to_date('01-03-2017', 'dd-mm-yyyy'))
14 );
Table created.
SQL>
SQL> insert into ev_log
2 select
3 rownum,
4 date '2017-02-28'-rownum/10,
5 rpad('AB',4000,'AB')
6 from dual
7 connect by level <= 1000;
1000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';
SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
SYS_IL0000088709C00003$$ INDEX PARTITION 65536
SYS_IL0000088709C00003$$ INDEX PARTITION 65536
SYS_IL0000088709C00003$$ INDEX PARTITION 65536
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
6 rows selected.
SQL>
SQL> update ev_log set event_data = empty_blob();
1000 rows updated.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> insert into ev_log
2 select
3 rownum,
4 date '2017-02-28'-rownum/10,
5 rpad('AB',4000,'AB')
6 from dual
7 connect by level <= 1000;
1000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';
SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
SYS_IL0000088709C00003$$ INDEX PARTITION 131072
SYS_IL0000088709C00003$$ INDEX PARTITION 131072
SYS_IL0000088709C00003$$ INDEX PARTITION 196608
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
6 rows selected.
SQL>
SQL> update ev_log set event_data = empty_blob();
2000 rows updated.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> insert into ev_log
2 select
3 rownum,
4 date '2017-02-28'-rownum/10,
5 rpad('AB',4000,'AB')
6 from dual
7 connect by level <= 1000;
1000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';
SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
SYS_IL0000088709C00003$$ INDEX PARTITION 196608
SYS_IL0000088709C00003$$ INDEX PARTITION 262144
SYS_IL0000088709C00003$$ INDEX PARTITION 327680
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 16777216
6 rows selected.
SQL>
SQL> update ev_log set event_data = empty_blob();
3000 rows updated.
SQL> commit;
Commit complete.
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';
SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
SYS_IL0000088709C00003$$ INDEX PARTITION 262144
SYS_IL0000088709C00003$$ INDEX PARTITION 327680
SYS_IL0000088709C00003$$ INDEX PARTITION 458752
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 16777216
6 rows selected.
SQL>
SQL>
SQL>
So you can see the lob grew as we updated and inserted. Let's now tell the database that we want to hold on to *nothing*, ie, pctversion = 0
SQL> create table ev_log
2 ( id float(63),
3 event_time timestamp (6) constraint sys_c0014416 not null enable,
4 event_data blob
5 )
6 lob (event_data) store as ev_log_lob( tablespace lobs disable storage in row pctversion 0)
7 partition by range (event_time)
8 interval(numtoyminterval(1, 'month'))
9 store in (users)
10 (
11 partition pos_data_p1 values less than (to_date('01-01-2017', 'dd-mm-yyyy')),
12 partition pos_data_p2 values less than (to_date('01-02-2017', 'dd-mm-yyyy')),
13 partition pos_data_p3 values less than (to_date('01-03-2017', 'dd-mm-yyyy'))
14 );
Table created.
SQL> insert into ev_log
2 select
3 rownum,
4 date '2017-02-28'-rownum/10,
5 rpad('AB',4000,'AB')
6 from dual
7 connect by level <= 1000;
1000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';
SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
SYS_IL0000088697C00003$$ INDEX PARTITION 65536
SYS_IL0000088697C00003$$ INDEX PARTITION 65536
SYS_IL0000088697C00003$$ INDEX PARTITION 65536
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
6 rows selected.
SQL>
SQL> update ev_log set event_data = empty_blob();
1000 rows updated.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> insert into ev_log
2 select
3 rownum,
4 date '2017-02-28'-rownum/10,
5 rpad('AB',4000,'AB')
6 from dual
7 connect by level <= 1000;
1000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';
SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
SYS_IL0000088697C00003$$ INDEX PARTITION 131072
SYS_IL0000088697C00003$$ INDEX PARTITION 131072
SYS_IL0000088697C00003$$ INDEX PARTITION 196608
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
6 rows selected.
SQL>
SQL> update ev_log set event_data = empty_blob();
2000 rows updated.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> insert into ev_log
2 select
3 rownum,
4 date '2017-02-28'-rownum/10,
5 rpad('AB',4000,'AB')
6 from dual
7 connect by level <= 1000;
1000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';
SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
SYS_IL0000088697C00003$$ INDEX PARTITION 196608
SYS_IL0000088697C00003$$ INDEX PARTITION 196608
SYS_IL0000088697C00003$$ INDEX PARTITION 262144
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
6 rows selected.
SQL>
SQL> update ev_log set event_data = empty_blob();
3000 rows updated.
SQL> commit;
Commit complete.
SQL>
SQL> select segment_name, segment_type, bytes from dba_segments where tablespace_name = 'LOBS';
SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
SYS_IL0000088697C00003$$ INDEX PARTITION 262144
SYS_IL0000088697C00003$$ INDEX PARTITION 262144
SYS_IL0000088697C00003$$ INDEX PARTITION 327680
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
EV_LOG_LOB LOB PARTITION 8388608
6 rows selected.
SQL>
SQL>
SQL>