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