Shrink of LOBs with Shrink Space Compact?

Hey everyone, been a while since I posted. A recent requirement to shrink LOB that had free space totaling to 300+ GB made me wonder if that can be done without causing any impact on another sessions.

You would read a lot on Oracle Support and various blogs about use of SHRINK SPACE COMPACT on a table, but unfortunately by the time of writing this, I didn't found anyone mentioned if it can be used for LOB as well.

Problem?
Shrink operation when running for several hours can affect user sessions during adjustment of HWM. Since you do not have control over completion time of an LOB shrink, this small duration of time can still impact your environment. Thus to have a greater control, you can breakdown shrink operation in two statements as below:

Alter table lob_tab modify LOB(data) (shrink space compact);
Alter table lob_tab modify LOB(data) (shrink space);

Test Case:
CREATE TABLE lob_tab (
  id NUMBER,
  data CLOB
)
LOB(data) STORE AS DATAVAL (DISABLE STORAGE IN ROW);

 Populate this table by running below statements several times randomly or in a loop:

INSERT INTO lob_tab VALUES (1, 'TEST1');
INSERT INTO lob_tab VALUES (3, 'TEST3');
INSERT INTO lob_tab VALUES (2, 'TEST2');

INSERT INTO lob_tab select * from lob_tab where id=2;
INSERT INTO lob_tab select * from lob_tab where id=1;
INSERT INTO lob_tab select * from lob_tab where id=3;

Commit;

Check Size of Table:

col segment_name for a25
col segment_type for a20
col gb for 9999.99
select segment_name, segment_type, sum(bytes/1024/1024) MB
from dba_segments
where owner='ORA10G'
group by segment_name, segment_type
order by 1,2
/

SEGMENT_NAME              SEGMENT_TYPE                 MB
------------------------- -------------------- ----------
DATAVAL                   LOBSEGMENT                  400
LOB_TAB                   TABLE                         2
SYS_IL0000046208C00002$$  LOBINDEX                      3

Delete some data and check fragmentation in Megabytes or Gigabytes using DBMS_SPACE.SPACE_USAGE procedure:

set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
total_fs_bytes number;
v_obj_name varchar2(40);
v_obj_type varchar2(10);
Cursor obj IS SELECT segment_name, segment_type FROM dba_segments
                WHERE segment_name in ('DATAVAL')
                ORDER BY segment_type;
begin
    OPEN obj;
    LOOP
    FETCH obj INTO v_obj_name, v_obj_type;
    EXIT WHEN obj%NOTFOUND;
    IF v_obj_type = 'LOBSEGMENT'
    THEN
        v_obj_type := 'LOB';
    END IF;
    dbms_space.space_usage ('ORA10G', v_obj_name , v_obj_type, v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks
    , v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
    total_fs_bytes:=round((v_fs1_bytes + v_fs2_bytes + v_fs3_bytes + v_fs4_bytes + v_unformatted_bytes)/1048576,2);
    dbms_output.put_line('Object-> ' || v_obj_name || ', Type-> ' || v_obj_type || ', Free MB-> ' || total_fs_bytes);
    END LOOP;
end;
/

Object-> DATAVAL, Type-> LOB, Free MB-> 107.57
 

Run Shrink
Alter table lob_tab modify LOB(data) (shrink space compact);

Check again using space usage script:
Object-> DATAVAL, Type-> LOB, Free MB-> 107.57

Free space didn't changed? - this is because shrink space compact has just adjusted data blocks and not the HWM. Now run shrink space to perform this:

Alter table lob_tab modify LOB(data) (shrink space);

Check again using space usage script:
Object-> DATAVAL, Type-> LOB, Free MB-> 2.11


Enjoy!