How to free space from lob securefile columns table in Oracle 21c without move

What is the SecureFiles LOB?

LOB are of two type BASICFILE and SECUREFILE.

The SecureFiles is used for large object (LOB) storage in Oracle 11g. The original LOB storage is known as BASICFILE which is used the default storage method, but the SECUREFILE keyword enables the new storage method, which allows encryption for security and space savings using compression.

Check the LOB is SECUREFILE or BASICFILE

select table_name, securefile from user_lobs where table_name like '%LOB%';

In previous version of Oracle, When we need to release space from the lob columns in the table, we need to move the lob object to the new tablespace to free the space from the tablespace.

Example: Move the column documents from old tablespace to new tablespace for release the space hold by lob column,

alter table employee move lob(documents) store as (tablespace newtablespace);

In Oracle 21c we can release the lob segments. This command will release space from the lob objects without moving to new tablespace. We can access the object during operation.

Shrink the SECUREFILE LOB in Oracle 21c

alter table employee modify lob (documents) (shrink space);
alter table employee shrink space cascade;

You can check the status in the following view during operations:

select * from V$SECUREFILE_SHRINK 

Check the lob segment blocks:

select ul.table_name,
       ul.column_name,
       ul.segment_name,
       us.blocks
from   user_lobs ul
       join user_segments us on us.segment_name = ul.segment_name;