reusing LOB segments
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: reusing LOB segments

  1. #1
    Join Date
    Oct 2001
    Location
    Buffalo, NY
    Posts
    46

    reusing LOB segments

    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!
    Tim Hussar

  2. #2
    Join Date
    Oct 2001
    Location
    Buffalo, NY
    Posts
    46
    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.
    Tim Hussar

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width