reusing LOB segments

[buffalodba]

I have a tablespace for LOB storage:

CREATE TABLESPACE TBS_LOBS DATAFILE '...'
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT MANUAL;

then I have a set of 5 tables, each with a LOB column, using this lob tablespace. Example of one of those tables:

create table fubar (msg_id number, msg_date date, msg clob)
tablespace tbs_a
...
lob (msg) store as fubar_lob
(tablespace tbs_lobs
enable storage in row
chunk 16384
pctversion 10
nocache
storage (initial 48K minextents 1 maxextents 2147483645
pctincrease 0 freelists 1 freelist groups 1
buffer_pool default)
...;

On average 50K rows a day are inserted into these 5 tables, and the majority of "msg" values is over 4K so the clobs are stored out-of-line. The clobs are only SELECTed, not updated (I know I should change the pctversion to 0). Every day at 1AM a script runs to delete rows from each of the 5 tables with msg_date older than 14 days. So each day 50K rows are being deleted from these 5 tables.

My problem is this: despite the daily deletes, my lob tablespace (tbs_lobs) is continuing to grow in size. Why are the lobindex and lobsegment segments of the deleted lobs not being reused by newly inserted lobs? I dont think changing the pctversion will alter this behavior. Does anyone have any ideas? TIA!

[buffalodba]

some additional info on this particular subject, I did find this posting on Ask Tom:

http://asktom.oracle.com/pls/ask/f?p...:7246820117571

however, I seem to be running into results different than the testing shown on that post. Tom claims the segments should be reused by the tables the segments are allocated to w/o needing to truncate any tables. I will continue to work on my end to understand what are the possible causes of my dilemma.

[tamilselvan]

CREATE TABLESPACE TBS_LOBS DATAFILE '...'
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT MANUAL;

My suggestion is do not use AUTOALLOCATE. Use UNIFORM EXTENT SIZE with SEGMENT SPACE MANAGEMENT MANUAL.

Tamil