shrink lob

-- create table (should be lmt tablespace or ORA-10635: Invalid segment or tablespace type will raise)
create table t_lob (id clob);
-- insert data
insert into t_lob select to_clob(lpad('x', 4000))||to_clob(lpad('x', 4000)) from dual connect by rownum <= 3000;
-- check size
select segment_name from dba_lobs where table_name = 'T_LOB';
SEGMENT_NAME
------------------------------
SYS_LOB0000015701C00001$$
select bytes/1024/1024 m from dba_segments where segment_name = 'SYS_LOB0000015701C00001$$';
M
------------------------------------
24.0
-- delete rows
delete from t_lob;
commit;
-- shrink
alter table t_lob shrink space cascade;
ORA-10636: ROW MOVEMENT is not enabled
--
alter table t_lob enable row movement;
alter table t_lob shrink space cascade;
Table altered.
select bytes/1024/1024 m from dba_segments where segment_name = 'SYS_LOB0000015701C00001$$';

M
------------------------------------
.0625