Moving blobs data to vaults is a viable strategy and here is a article for it: https://www.ptc.com/en/support/article/CS68716?as=1
Recently, I was asked to take a look into a ORA-10635 error when running a query against Oracle to do the shrinking.
1
2
-- This looks correct, but it fails in 19c
ALTER TABLE QUEUEENTRY MODIFY LOB (BLOB$ARGS) (SHRINK SPACE);
Instead of reclaimed space, you get hit with: ORA-10635: Invalid segment or tablespace type
What’s happening?
- SecureFile LOBs: Most modern applications (including Windchill) store BLOBs as SecureFiles for better performance and encryption.
- Version Limitation: Oracle 19c strictly does not support the SHRINK SPACE command on SecureFile segments. Oracle didn’t introduce that capability until the 21c release.
Even if your tablespace uses Automatic Segment Space Management (ASSM), the SecureFile architecture in 19c requires a full segment rebuild to reclaim fragmented “white space.”
The Solution
Since we cannot “shrink” the existing segment in place, we must “move” it. This process creates a brand-new LOB segment, copies only the active data into it, and then drops the old, bloated segment.
Identify the segment
1
2
3
4
5
6
7
8
SELECT
ul.table_name,
ul.column_name,
us.segment_name,
(us.blocks * 8192) / 1024 / 1024 as size_mb
FROM user_lobs ul
JOIN user_segments us ON us.segment_name = ul.segment_name
WHERE ul.table_name = 'QUEUEENTRY';
Execute the Move
1
2
3
4
5
-- WARNING: This is an OFFLINE operation in 19c.
-- It will lock the QUEUEENTRY table during execution.
ALTER TABLE QUEUEENTRY
MOVE LOB ("BLOB$ARGS")
STORE AS SECUREFILE;
Check results
1
2
3
4
5
6
7
8
9
SELECT segment_name, segment_type, blocks, bytes / 1024 / 1024 AS size_mb, tablespace_name FROM user_segments WHERE segment_name = (SELECT segment_name FROM user_lobs WHERE table_name = 'QUEUEENTRY' AND column_name = 'BLOB$ARGS');
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE BLOCKS SIZE_MB TABLESPACE_NAME
------------------ ---------- ---------- ------------------------------
SYS_LOB0000025562C00004$$
LOBSEGMENT 2048 16 BLOBS
Note
This is also a good YouTube video describing this situation: https://www.youtube.com/watch?v=o4hUera-5tc&t=1s