Shrink Lob Segment

SET LINES 200
SET pages 1000
COLUMN c1 heading "Owner"  format a15
COLUMN c2 heading "Name"   format a35
COLUMN c3 heading "Type"   format a15
COLUMN c4 heading "MB"      format 99999

SELECT
  owner c1,
  segment_name c2,
  segment_type c3,
  (bytes/1024/1024) c4
FROM dba_segments
WHERE tablespace_name='SYSAUX'
AND (bytes/1024/1024) > 50
ORDER BY c4 DESC;


-- Enable row movement.
EXEC dbms_pdb.exec_as_oracle_script('alter table bccollect.coll_todo enable row movement');

-- Recover space and amend the high water mark (HWM).
EXEC dbms_pdb.exec_as_oracle_script('alter table bccollect.coll_todo shrink space');

-- Recover space, but don't amend the high water mark (HWM).
EXEC dbms_pdb.exec_as_oracle_script('alter table bccollect.coll_todo shrink space compact');

-- Recover space for the object and all dependant objects.
EXEC dbms_pdb.exec_as_oracle_script('alter table bccollect.coll_todo shrink space cascade');