Oracle 19c cannot shrink blob column

Question

I deleted a bunch of old data from a schema, including BLOB data, and afterwards I tried to shrink and compact everything. I managed to shrink all tables (enable row movement, shrink space compact, disable row movement), except the one BLOB column, which is stored on a separate segment:

SELECT
 segment_name,
 SUM(bytes) / 1024 / 1024 / 1024 AS "GB_SIZE"
FROM
 dba_segments
WHERE
 owner = 'MY_OWNER'
GROUP BY
 segment_name
ORDER BY
 SUM(bytes) / 1024 / 1024 / 1024 DESC;

Segment in question:

SEGMENT_NAME GB_SIZE
SYS_LOB0000072887C00005$$ 0,35955810546875

Note: this is a test DB, in prod DB, this is about 660 GB.

When I try to shrink it, I get this error:

ALTER TABLE my_table MODIFY LOB ( my_blob ) ( SHRINK SPACE );
Error starting at line : 1 in command -
alter table my_table modify lob (my_blob) (shrink space)
Error report -
ORA-10635: Invalid segment or tablespace type
10635. 00000 - "Invalid segment or tablespace type"
*Cause: Cannot shrink the segment because it is not in auto segment space
 managed tablespace or it is not a data, index or lob segment.
*Action: Check the tablespace and segment type and reissue the statement

Segment details:

SELECT
 *
FROM
 dba_segments
WHERE
 segment_name = 'SYS_LOB0000072887C00005$$';

Relevant details:

• Segment type: LOBSEGMENT
• Segment subtype: SECUREFILE
• Tablespace name: MY_DATA

Tablespace details:

SELECT
 *
FROM
 dba_tablespaces
WHERE
 tablespace_name = 'MY_DATA';

Relevant details:

• Extent management: LOCAL
• Allocation type: SYSTEM
• Segment space management: AUTO

Exact Oracle version:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

As far as I can tell, everything is as it should be, so I'm not sure what is missing.

Answer 1

The SHRINK SPACE option is not supported for Securefile LOBs.

You may use DBMS_REDEFINITION (mostly online) or ALTER TABLE ... MOVE LOB (...) (offline, table is locked during move).

ORA-10635: Invalid Segment or Tablespace Type Error When Attempting To Shrink SOA-INFRA LOB [To Free Tablespace] (Doc ID 2382484.1)

• Gábor Major: Thanks, I didn't find this info for some reason. I'll try with the DBMS_REDEFINITION package.