0

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.

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)

1
  • Thanks, I didn't find this info for some reason. I'll try with the DBMS_REDEFINITION package. – Gábor Major Jun 2 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.