Reclaim deleted LOB data storage

I was helping a client purge obsolete data and reclaim some space in an OLAP database.
We stuck on a BLOB column segment. It took me a couple hours to find the solution.

Here is the solution demo.

Create a table with BLOB column,

drop table t2 purge;

CREATE TABLE t2
(
 n1 NUMBER(10),
 d1 date,
 b1 BLOB,
 CONSTRAINT t2_PK PRIMARY KEY (n1)
  USING INDEX TABLESPACE index_auto
) 
TABLESPACE data_auto;

Displays the large objects (LOBs) contained in tables

select b.TABLE_NAME, b.COLUMN_NAME, b.SEGMENT_NAME, b.TABLESPACE_NAME, b.INDEX_NAME
from user_lobs b;

SELECT SEGMENT_NAME, segment_type, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS
FROM user_SEGMENTS
WHERE segment_type like '%LOB%'
ORDER BY SEGMENT_NAME;

SELECT b.TABLE_NAME, b.COLUMN_NAME, b.SEGMENT_NAME, b.TABLESPACE_NAME, b.INDEX_NAME
 ,s.bytes, s.blocks, s.extents
FROM user_lobs b, user_segments s
WHERE b.table_name = 'T2'
and b.column_name = 'B1'
and s.segment_type like 'LOB%'
and s.segment_name = b.segment_name;

Test Shrink a BASICFILE LOB segment only

truncate table t2;

declare
    l_blob blob;
    l_size number := 32700;
begin
  for i in 1 .. 15
  loop
    insert into t2(n1,b1) values (i, empty_blob() ) returning b1 into l_blob;
    dbms_lob.writeappend( l_blob, l_size, utl_raw.cast_to_raw(rpad('*',l_size,'*')));
  end loop;
  commit;
end;
/

delete t2;
commit;

SELECT b.TABLE_NAME, b.COLUMN_NAME, b.SEGMENT_NAME, b.TABLESPACE_NAME, b.INDEX_NAME
 ,s.bytes, s.blocks, s.extents
FROM user_lobs b, user_segments s
WHERE b.table_name = 'T2'
and b.column_name = 'B1'
and s.segment_type like 'LOB%'
and s.segment_name = b.segment_name;

     BYTES     BLOCKS    EXTENTS
---------- ---------- ----------
    720896         88         11

ALTER TABLE t2 MODIFY LOB (b1) (SHRINK SPACE);

SELECT b.TABLE_NAME, b.COLUMN_NAME, b.SEGMENT_NAME, b.TABLESPACE_NAME, b.INDEX_NAME
 ,s.bytes, s.blocks, s.extents
FROM user_lobs b, user_segments s
WHERE b.table_name = 'T2'
and b.column_name = 'B1'
and s.segment_type like 'LOB%'
and s.segment_name = b.segment_name;

     BYTES     BLOCKS    EXTENTS
---------- ---------- ----------
     65536          8          1

Note

Shrink command will generate about same size of redo/archive logs as LOB storage space size.

Reference.

Shrink a table and all of its dependent segments (including BASICFILE LOB segments):

ALTER TABLE t2 ENABLE ROW MOVEMENT;
ALTER TABLE t2 SHRINK SPACE CASCADE;

Shrink a BASICFILE LOB segment only:

ALTER TABLE t2 MODIFY LOB (b1) (SHRINK SPACE);