Oracle 19c cannot shrink blob column

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , , ,

Leave a Reply