Oracle – Shrink LOB columns

--identifying lob segments using more space:
select sg.segment_name, lb.table_name , lb.column_name, sum(sg.bytes)/(1024*1024) mb
from dba_segments sg, dba_lobs lb
where sg.segment_name = lb.segment_name
group by sg.segment_name, lb.table_name , lb.column_name order by mb desc;

--generating shrink command to reduce the lob sizes
select sg.segment_name, lb.owner, lb.table_name, lb.column_name, sum (sg.bytes)/(1024*1024) mb
'alter table ||lb.owner||'.'|| lb.table_name || modify lob (' || lb.column_name || )
(shrink space) ;' shrink_command
from dba_segments sg, dba_lobs lb
where sg.segment_name = lb.segment_name
group by sg.segment_name, lb.owner, lb.table_name, lb.column_name order by mb desc;

--example:
alter table hr.employee modify lob (emp_address) (shrink space);

Reference:-

https://oracle-base.com/articles/misc/alter-table-shrink-space-online