create tablespace test_lob_shrink datafile '*******' size 100M autoextend on next 100M maxsize unlimited 
extent management local autoallocate segment space management auto;


create table test_ers_lob(id number(20),cl clob) tablespace test_lob_shrink lob(cl) store as basicfile;
declare
begin
  for i in 1..10000 loop
    insert into test_ers_lob values(i,rpad('*',5000,'*'));
    end loop;
    commit;
    end;

   До удаления данных:
    
    select sum(bytes)/1024/1024 from dba_extents where segment_name='SYS_LOB0000225468C00002$$';--80 MB --120после erase и cascade 86 после shrink lob
     select sum(bytes)/1024/1024 from dba_extents where segment_name='TEST_ERS_LOB';-- 0,625

   declare
   clo clob;
   len number(38);
   begin
      for i in (select * from test_ers_lob where id<5000 for update)
        loop
          clo:=i.cl;
          len:=dbms_lob.getlength(clo);
         dbms_lob.erase(clo,len);
          end loop;
          end;

После удаления 

select sum(bytes)/1024/1024 from dba_extents where segment_name='SYS_LOB0000225468C00002$$';--120 MB После удаления  и cascade 86 после shrink lob
     select sum(bytes)/1024/1024 from dba_extents where segment_name='TEST_ERS_LOB';-- 0,625

потом делалось 

alter table test_ers_lob enable row movement;  
alter table test_ers_lob shrink space cascade;

и ALTER TABLE test_ers_lob MODIFY LOB (CL) (SHRINK SPACE);

select sum(bytes)/1024/1024 from dba_extents where segment_name='SYS_LOB0000225468C00002$$';--120 MB после shrink space cascade и  86 MB после shrink lob
select sum(bytes)/1024/1024 from dba_extents where segment_name='TEST_ERS_LOB';-- 0,625

    select tablespace_name,used_space*8/1024,tablespace_size*8/1024/1024,used_percent from dba_tablespace_usage_metrics where lower(tablespace_name)='test_lob_shrink';
 --1	TEST_LOB_SHRINK	81,6875	31,9999847412109	0,249290585179608 - после заливки данных
--1	TEST_LOB_SHRINK	122,125	31,9999847412109	0,372696100566912 - после удаления
--1	TEST_LOB_SHRINK	122,125	31,9999847412109	0,372696100566912 - после shrink space cascade
--1	TEST_LOB_SHRINK	88,25	31,9999847412109	0,269317755373838 - после ALTER TABLE test_ers_lob MODIFY LOB (CL) (SHRINK SPACE);