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.
