Lob Segment Size Increases in Dictionary Managed Tablespace: Freelist Blocks Are Not Used (Doc ID 415845.1)

Last updated on DECEMBER 07, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.7
This problem can occur on any platform.

Symptoms

Every night about a huge number of blocks are deleted in a lob segment, then nearly the same number of blocks are inserted, but only about 10% of the blocks are reused from the freelist. All other blocks are allocated from new extents.

SET serveroutput ON;
   
DECLARE     l_id     NUMBER;
        l_blocks  NUMBER;
        l_size  NUMBER;
        l_owner  VARCHAR2(30);
        l_object  VARCHAR2(30);
        l_type  VARCHAR2(30);
BEGIN
      l_owner:='SCOTT';
      l_object:='SCOTT$LOB1';
      l_type:='LOB';
      --
      SELECT SUM(bytes) INTO l_size FROM user_extents WHERE  segment_name=l_object;
   
      dbms_output.put_line('Object: '||l_owner||'.'||l_object||TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
      dbms_output.put_line('Segment_size: '||l_size);
      FOR l_id IN 0..99 LOOP
        BEGIN
          DBMS_SPACE.FREE_BLOCKS(segment_owner=>l_owner,
                 segment_name=>l_object,
                 segment_type=>l_type,
                 freelist_group_id=>l_id,
                 free_blks=>l_blocks);
          dbms_output.put_line('Free list: '||l_id||' '||' Blocks: '||l_blocks);
      EXCEPTION WHEN OTHERS THEN EXIT;
      END;
      END LOOP;
    END;
/

Object: SCOTT.SCOTT$LOB1   2007-01-16 08:39:52
Segment_size: 97 644 216 320
Free list: 0 Blocks: 2 795 175
Free list: 1 Blocks: 1 005 246
Free list: 2 Blocks: 1 402 120
Free list: 3 Blocks: 2 926 092

Object: SCOTT.SCOTT$LOB1   2007-01-17 08:58:16
Segment_size: 100 915 773 440
Free list: 0 Blocks: 2 796 405
Free list: 1 Blocks: 1 005 249
Free list: 2 Blocks: 1 362 886
Free list: 3 Blocks: 3 322 994

The segment is increasing so fast that it wastes too much disk space: this leads to severe disk space wastage.

Attributes of the lob segment:

CHUNK : 8192
PCTVERSION : 10
RETENTION : 18000
Enabled In row : NO

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms