Does DEALLOCATE UNUSED or SHRINK SPACE will free space occupied by LOB segment?

Lets check how it works. My env is DB 19.20@Linux-x64

1) I created the table with 4 LOB columns of 4 different LOB types: BASICFILE BLOB, BASICFILE CLOB, SECUREFILE BLOB and SECUREFILE CLOB.
2) Filled LOB columns with some amount of data and
3) Deleted the LOB-data with empty() function
4) Then obtain the space occupied by LOB-segments and
5) Try commands to free occupied LOB-space:
- ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (DEALLOCATE UNUSED [keep 0]);
- ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (SHRINK SPACE CASCADE);

The script below:
--------------------------------------
set lines 200
set pages 99

conn  / as sysdba
drop user test_lob cascade;
drop tablespace test_lob including contents and datafiles;

create tablespace test_lob extent management local segment space management auto datafile '/oradata/ORCL/test_lob.dbf' size 100m autoextend off;
                             
create user test_lob identified by test_lob default tablespace test_lob;
grant dba to test_lob;

conn test_lob/test_lob

create table lob_deallocate
   (    va varchar2(100 BYTE)
       ,BASICFILE_BLOB BLOB
       ,BASICFILE_CLOB CLOB
       ,SECUREFILE_BLOB BLOB
       ,SECUREFILE_CLOB CLOB
   )  
        TABLESPACE test_lob
  LOB (BASICFILE_BLOB)  STORE AS BASICFILE  BF_BLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING )
  LOB (BASICFILE_CLOB)  STORE AS BASICFILE  BF_CLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING )
  LOB (SECUREFILE_BLOB) STORE AS SECUREFILE SF_BLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING )
  LOB (SECUREFILE_CLOB) STORE AS SECUREFILE SF_CLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING ) ;

-- Fill the table with some data to blow up LOB-segments
declare
    l_data long := rpad('*',30000,'*');
begin
        for i in 1 .. 100
        loop
              insert into lob_deallocate values ( i,
              utl_raw.cast_to_raw(l_data),
              utl_raw.cast_to_raw(l_data),
              utl_raw.cast_to_raw(l_data),
              utl_raw.cast_to_raw(l_data) );
        end loop;
end;
/
commit;

-- Measure the LOB-segments
select  sum(dbms_lob.getlength(BASICFILE_BLOB))
       ,sum(dbms_lob.getlength(BASICFILE_CLOB))
       ,sum(dbms_lob.getlength(SECUREFILE_BLOB))
       ,sum(dbms_lob.getlength(SECUREFILE_CLOB))
from lob_deallocate;

col tablespace_name for a6
col segment_name for a28
select SEGMENT_NAME,SEGMENT_TYPE,SEGMENT_SUBTYPE, BYTES, BLOCKS,EXTENTS, INITIAL_EXTENT,NEXT_EXTENT from user_segments;
select SEGMENT_NAME,SEGMENT_SUBTYPE,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where SEGMENT_TYPE='LOBSEGMENT';

select * from USER_LOBS;

-- Delete the LOB-data
update lob_deallocate set
          BASICFILE_BLOB=empty_blob(),
          BASICFILE_CLOB=empty_clob(),
          SECUREFILE_BLOB=empty_blob(),
          SECUREFILE_CLOB=empty_clob();
commit;

-- Measure the LOB-segments
select  sum(dbms_lob.getlength(BASICFILE_BLOB))
       ,sum(dbms_lob.getlength(BASICFILE_CLOB))
       ,sum(dbms_lob.getlength(SECUREFILE_BLOB))
       ,sum(dbms_lob.getlength(SECUREFILE_CLOB))
from lob_deallocate;

select SEGMENT_NAME,SEGMENT_SUBTYPE,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where SEGMENT_TYPE='LOBSEGMENT';

ALTER TABLE lob_deallocate ENABLE ROW MOVEMENT;

-- try ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (DEALLOCATE UNUSED [keep 0]);
-- try ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (SHRINK SPACE CASCADE);

ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_BLOB)  (DEALLOCATE UNUSED keep 0);
ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_CLOB)  (DEALLOCATE UNUSED keep 0);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_BLOB) (DEALLOCATE UNUSED keep 0);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_CLOB) (DEALLOCATE UNUSED keep 0);

-- Measure the LOB-segments
select SEGMENT_NAME,SEGMENT_SUBTYPE,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where SEGMENT_TYPE='LOBSEGMENT';


------------------------------------------

Tablespace TEST_LOB created.
User TEST_LOB created.
Grant succeeded.
Connected.
Table LOB_DEALLOCATE created.
PL/SQL procedure successfully completed.
Commit complete.

SUM(DBMS_LOB.GETLENGTH(BASICFILE_BLOB)) SUM(DBMS_LOB.GETLENGTH(BASICFILE_CLOB)) SUM(DBMS_LOB.GETLENGTH(SECUREFILE_BLOB)) SUM(DBMS_LOB.GETLENGTH(SECUREFILE_CLOB))
_______________________________________ _______________________________________ ________________________________________ ________________________________________
                                3000000                                 3276700                                  3000000                                  3276700


               SEGMENT_NAME    SEGMENT_TYPE    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
___________________________ _______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB                     LOBSEGMENT      ASSM                   4194304       512         19             65536        1048576
BF_CLOB                     LOBSEGMENT      ASSM                   8388608      1024         23             65536        1048576
LOB_DEALLOCATE              TABLE           ASSM                     65536         8          1             65536        1048576
SF_BLOB                     LOBSEGMENT      SECUREFILE             5439488       664          7            106496        1048576
SF_CLOB                     LOBSEGMENT      SECUREFILE            16973824      2072         11            106496        1048576
SYS_IL0000077702C00002$$    LOBINDEX        ASSM                     65536         8          1             65536        1048576
SYS_IL0000077702C00003$$    LOBINDEX        ASSM                     65536         8          1             65536        1048576
SYS_IL0000077702C00004$$    LOBINDEX        ASSM                     65536         8          1             65536        1048576
SYS_IL0000077702C00005$$    LOBINDEX        ASSM                     65536         8          1             65536        1048576

9 rows selected.


   SEGMENT_NAME    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB         ASSM                   4194304       512         19             65536        1048576
BF_CLOB         ASSM                   8388608      1024         23             65536        1048576
SF_BLOB         SECUREFILE             5439488       664          7            106496        1048576
SF_CLOB         SECUREFILE            16973824      2072         11            106496        1048576


       TABLE_NAME        COLUMN_NAME    SEGMENT_NAME    TABLESPACE_NAME                  INDEX_NAME    CHUNK    PCTVERSION    RETENTION    FREEPOOLS    CACHE    LOGGING    ENCRYPT    COMPRESSION    DEDUPLICATION    IN_ROW             FORMAT    PARTITIONED    SECUREFILE    SEGMENT_CREATED    RETENTION_TYPE    RETENTION_VALUE
_________________ __________________ _______________ __________________ ___________________________ ________ _____________ ____________ ____________ ________ __________ __________ ______________ ________________ _________ __________________ ______________ _____________ __________________ _________________ __________________
LOB_DEALLOCATE    BASICFILE_BLOB     BF_BLOB         TEST_LOB           SYS_IL0000077702C00002$$        8192                        900              NO       YES        NONE       NONE           NONE             NO        NOT APPLICABLE     NO             NO            YES                YES                                  
LOB_DEALLOCATE    BASICFILE_CLOB     BF_CLOB         TEST_LOB           SYS_IL0000077702C00003$$        8192                        900              NO       YES        NONE       NONE           NONE             NO        ENDIAN NEUTRAL     NO             NO            YES                YES                                  
LOB_DEALLOCATE    SECUREFILE_BLOB    SF_BLOB         TEST_LOB           SYS_IL0000077702C00004$$        8192                                         NO       YES        NO         NO             NO               NO        NOT APPLICABLE     NO             YES           YES                DEFAULT                              
LOB_DEALLOCATE    SECUREFILE_CLOB    SF_CLOB         TEST_LOB           SYS_IL0000077702C00005$$        8192                                         NO       YES        NO         NO             NO               NO        ENDIAN NEUTRAL     NO             YES           YES                DEFAULT                              


100 rows updated.


Commit complete.


   SUM(DBMS_LOB.GETLENGTH(BASICFILE_BLOB))    SUM(DBMS_LOB.GETLENGTH(BASICFILE_CLOB))    SUM(DBMS_LOB.GETLENGTH(SECUREFILE_BLOB))    SUM(DBMS_LOB.GETLENGTH(SECUREFILE_CLOB))
__________________________________________ __________________________________________ ___________________________________________ ___________________________________________
                                         0                                          0                                           0                                           0

Before DEALLOCATE UNUSED:

   SEGMENT_NAME    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB         ASSM                   4194304       512         19             65536        1048576
BF_CLOB         ASSM                   8388608      1024         23             65536        1048576
SF_BLOB         SECUREFILE             5439488       664          7            106496        1048576
SF_CLOB         SECUREFILE            16973824      2072         11            106496        1048576


Table LOB_DEALLOCATE altered.  -- row movement


Table LOB_DEALLOCATE altered.
Table LOB_DEALLOCATE altered.
Table LOB_DEALLOCATE altered.
Table LOB_DEALLOCATE altered.

After DEALLOCATE UNUSED:

   SEGMENT_NAME    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB         ASSM                   4194304       512         19             65536        1048576
BF_CLOB         ASSM                   8388608      1024         23             65536        1048576
SF_BLOB         SECUREFILE             5439488       664          7            106496        1048576
SF_CLOB         SECUREFILE            16973824      2072         11            106496        1048576

As we see DEALLOCATE UNUSED doesn''t free the space occupied by LOBs.

------------------------------------------

The 2nd run is for SHRINK SPACE, i changed 4 lines in my script:

ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_BLOB)  (SHRINK SPACE CASCADE);
ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_CLOB)  (SHRINK SPACE CASCADE);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_BLOB) (SHRINK SPACE CASCADE);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_CLOB) (SHRINK SPACE CASCADE);

Before SHRINK:
   SEGMENT_NAME    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB         ASSM                   4194304       512         19             65536        1048576
BF_CLOB         ASSM                   8388608      1024         23             65536        1048576
SF_BLOB         SECUREFILE             5373952       656          6            106496        1048576
SF_CLOB         SECUREFILE            16908288      2064         10            106496        1048576


Table LOB_DEALLOCATE_UNUSED altered. -- row movement
Table LOB_DEALLOCATE_UNUSED altered.
Table LOB_DEALLOCATE_UNUSED altered.
Table LOB_DEALLOCATE_UNUSED altered.
Table LOB_DEALLOCATE_UNUSED altered.

After SHRINK:

   SEGMENT_NAME    SEGMENT_SUBTYPE       BYTES    BLOCKS    EXTENTS    INITIAL_EXTENT    NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB         ASSM                     65536         8          1             65536        1048576
BF_CLOB         ASSM                     65536         8          1             65536        1048576
SF_BLOB         SECUREFILE             5373952       656          6            106496        1048576
SF_CLOB         SECUREFILE            16908288      2064         10            106496        1048576

-----------------------------------------------------


SHRINK works for BASICFILE only:

          | BLOB | CLOB |
-------------------------
BASICFILE | YES  | YES  |
SECUREFILE| NO   | NO   |
--------------------------

ORA-01555 after unclear shutdown and How to repair a CONFLUENCE ?

After holidays at NY 2017 the CONFLUENCE won't work.
In its logs the application say something about ORA-01555 and alert show the unclear shutdown.

There were no bad messages in database alert logs and any corrupted blocks, however.

To check the data integrity I run the full export of database. And Full export show the bad table:

ORA-31693: Table data object "CONFLU"."BANDANA" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

The command

set long 9999999
select * from conflu.bandana ;
has finished successfully and show all 297 rows as if there is no error !


But the export raised the error ORA-01555.

The solution is:

create table corrupted_lob_data (corrupted_rowid rowid);
declare
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
n number;
begin
  for cursor_lob in (select rowid r, bandanavalue from conflu.bandana) loop
    begin
      n := dbms_lob.instr (cursor_lob.bandanavalue, hextoraw ('889911')) ;
exception
when error_1578 then
insert into corrupted_lob_data values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupted_lob_data values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupted_lob_data values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/

This script checks any LOB data in the corrupted table.

Then I found a specific bad row:   select * from conflu.bandana where rowid='AAAUnkAAFAAABiEAAG';


And specific filed "bandanavalue" in this row.
Then I updated this field:

update conflu.bandana set   bandanavalue = empty_clob() where rowid='AAAUnkAAFAAABiEAAG';
commit;

Now Confluence works!