Reclaiming LOB space in Oracle

Reclaiming space in Oracle can sometimes be a bit of a "problem", not really a problem it just works in a funny way. It's a quite common question I get and users are usually happy with a manual alter table table_name shrink space compact;, but what do we do for lobs?
We need a manual reclaim for the lob column.

A little demo:

(spinner1)oracle@spinner[~/lob_test]$ rsqlplus hlinden/hlinden

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jun 14 12:19:02 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

-- Create a table and sequence to play with
HLINDEN@spinner1> create table lob_test (id number, data blob);

Table created.

HLINDEN@spinner1> create sequence lob_test_seq;

Sequence created.

-- Load 50 rows with 1.5Mb blobs (see code bellow)
HLINDEN@spinner1> @lobload

PL/SQL procedure successfully completed.

-- Find out what our lob segment is called
HLINDEN@spinner1> select object_name,object_type from user_objects where
  2  created>sysdate-interval '5' minute and object_type='LOB';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SYS_LOB0000199575C00002$$      LOB

-- Display the current size of the lob segment
HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
        75

-- Ok, let's delete those blobs and see what the size is after
HLINDEN@spinner1> delete from lob_test purge;

50 rows deleted.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
        75

-- Still 75Mb, hm, perhaps it recycled if we insert more data?

HLINDEN@spinner1> @lobload

PL/SQL procedure successfully completed.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
       150

-- Nope, not recycled. We need to issue a shrink command to free up the 
-- space immediately
HLINDEN@spinner1> delete from lob_test;

50 rows deleted.

HLINDEN@spinner1> alter table lob_test modify lob (data) (shrink space);

Table altered.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
         0
-- All gone!

My simple blob loading code:

DECLARE
  src_file      BFILE := bfilename('TMP', 'data.dat');
  dst_file      BLOB;
  lgh_file      BINARY_INTEGER;
  cur_id        NUMBER(10);
BEGIN
  FOR i IN 1..50
  LOOP
    INSERT INTO lob_test(id,data) VALUES(lob_test_seq.nextval,empty_blob())
          RETURNING id into cur_id;
    -- lock record
    SELECT data INTO dst_file FROM lob_test WHERE id=cur_id FOR UPDATE;

    dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
    lgh_file := dbms_lob.getlength(src_file);
    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
    dbms_lob.fileclose(src_file);
  END LOOP;
END;
/