I’ve been working with LOBs for years now, and trust me, shrinking them has always been a headache. Anyone who has ever tried ALTER TABLE … SHRINK SPACE on a big SecureFiles LOB knows the pain … blocking sessions, unexpected waits, and sometimes that lovely ORA-1555 popping up at the worst time. Every DBA eventually gets into that situation where a LOB segment is 200 GB on disk but only 10 GB of real data remains. You delete rows… but the space never comes back unless you manually shrink it, which itself can cause more issues.
With the introduction of Automatic SecureFiles Shrink, Oracle really made a DBA’s life easier. This feature, which first came out in 23ai, quietly frees up unused LOB space in the background without disrupting your workload. I wanted to see how it behaves in a real scenario, so I set up a small lab and tested it out. Here’s the whole experiment, raw and simple.
Lets do a demo and understand how this new feature works … I spun up a fresh PDB and made a small tablespace just for this test. Nothing fancy.
CREATE TABLESPACE lobts
DATAFILE '/u02/oradata/LOBTS01.dbf'
SIZE 1G AUTOEXTEND ON NEXT 256M;
Tablespace created.
-- Table with a securefile LOB based column.
CREATE TABLE tst_securefile_lob
(
id NUMBER,
lob_data CLOB
)
LOB (lob_data) STORE AS SECUREFILE (
TABLESPACE lobts
CACHE
);
Table created.
SELECT table_name, column_name, securefile
FROM user_lobs
WHERE table_name='TST_SECUREFILE_LOB';
TABLE_NAME COLUMN_NAME SECUREFILE
------------------- ------------ ----------
TST_SECUREFILE_LOB LOB_DATA YES
Next, I inserted a good amount of junk data around 10,000 rows of random CLOB strings. I wanted the LOB segment to be big enough to see clear differences after shrink.
BEGIN
FOR r IN 1 .. 10 LOOP
INSERT INTO tst_securefile_lob (id, lob_data)
SELECT r*100000 + level,
TO_CLOB(DBMS_RANDOM.STRING('x', 32767))
FROM dual
CONNECT BY level <= 1000;
COMMIT;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SELECT COUNT(*) FROM tst_securefile_lob;
COUNT(*)
----------
10000
SELECT ul.segment_name,
us.blocks,
ROUND(us.bytes/1024/1024,2) AS mb
FROM user_lobs ul
JOIN user_segments us
ON us.segment_name = ul.segment_name
WHERE ul.table_name = 'TST_SECUREFILE_LOB';
SEGMENT_NAME BLOCKS MB
------------------------ -------- --------
SYS_LOB0001234567C00002$ 131072 1024.00
-- After stats and a quick check in USER_SEGMENTS, the LOB segment was showing a nice chunky size.
-- Then I deleted almost everything
-- Now the table will have very few rows left, but the LOB segment was still the same size. As usual.
DELETE FROM tst_securefile_lob
WHERE id < 900000;
COMMIT;
9990 rows deleted.
Commit complete.
-- Checking LOB Internal Usage (Before Auto Shrink)
EXEC show_securefile_space(USER, 'SYS_LOB0001234567C00002$');
Segment blocks = 131072 bytes=1073741824
Used blocks = 10240 bytes=83886080
Expired blocks = 110592 bytes=905969664
Unexpired blocks = 10240 bytes=83886080
-- This clearly shows almost the entire segment is expired/free but not reclaimed.
-- Checking Auto Shrink Statistics (Before Enabling)
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%Auto SF SHK%'
ORDER BY name;
NAME VALUE
-------------------------------------- ------
Auto SF SHK failures 0
Auto SF SHK segments processed 0
Auto SF SHK successful 0
Auto SF SHK total number of tasks 0
Turning on Automatic Shrink — By default this feature is OFF, so I enabled it:
EXEC DBMS_SPACE.SECUREFILE_SHRINK_ENABLE;
PL/SQL procedure successfully completed.
That’s literally it. No parameters, no tuning, nothing else. Just enable.
Automatic SecureFiles Shrink does not run immediately after you delete data. Oracle requires that a SecureFiles LOB segment be idle for a specific amount of time before it becomes eligible for shrinking, and the default idle-time limit is 1,440 minutes (24 hours). “Idle” means that no DML or preallocation activity has occurred on that LOB during that period. Once the segment meets this condition, Oracle considers it during its automatic background shrink task, which is part of AutoTask and runs every 30 minutes with a defined processing window.
When the task executes, it attempts to shrink eligible segments, but it does so gradually and in small increments using a trickle-based approach, rather than reclaiming all possible space in a single operation. This incremental behavior is deliberate: it reduces impact on running workloads and avoids heavy reorganization all at once. Only segments that meet all selection criteria .. such as having sufficient free space above the defined thresholds and not using RETENTION MAX … are processed. Because of this incremental design and the eligibility rules, the full space reclamation process can span multiple background cycles.
Some of the internal hidden/underscore params that can be used to control these limits (not unless support asked you to do or you are dealing a lab system)
Parameter Default_Value Session_Value Instance_Value IS_SESSION_MODIFIABLE IS_SYSTEM_MODIFIABLE
------------------------------------------- --------------- ---------------- ---------------- ----------------------- ---------------------
_ktsls_autoshrink_seg_idle_seconds 86400 86400 86400 FALSE IMMEDIATE
_ktsls_autoshrink_seg_pen_seconds 86400 86400 86400 FALSE IMMEDIATE
_ktsls_autoshrink_trickle_mb 5 5 5 FALSE IMMEDIATE
Okay, lets check out post chnaghe outputs, what automatic LOB shrink does to our test object.
SELECT ul.segment_name,
us.blocks,
ROUND(us.bytes/1024/1024,2) AS mb
FROM user_lobs ul
JOIN user_segments us
ON us.segment_name = ul.segment_name
WHERE ul.table_name='TST_SECUREFILE_LOB';
SEGMENT_NAME BLOCKS MB
------------------------ -------- --------
SYS_LOB0001234567C00002$ 40960 320.00
Note: From ~1024 MB down to ~320 MB. Auto shrink worked 🙂
-- DBMS_SPACE Usage (After Auto Shrink)
EXEC show_securefile_space(USER, 'SYS_LOB0001234567C00002$');
Segment blocks = 40960 bytes=335544320
Used blocks = 9216 bytes=75497472
Expired blocks = 6144 bytes=50331648
Unexpired blocks = 9216 bytes=75497472
Notee: Expired blocks dropped from 110k --> 6k. This confirms auto shrink freed most of the fragmented space.
-- After the task is run.
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%Auto SF SHK%'
ORDER BY name;
NAME VALUE
-------------------------------------- ------
Auto SF SHK failures 0
Auto SF SHK segments processed 1
Auto SF SHK successful 1
Auto SF SHK total number of tasks 1
SELECT owner,
segment_name,
shrunk_bytes,
attempts,
last_shrink_time
FROM v$securefile_shrink;
OWNER SEGMENT_NAME SHRUNK_BYTES ATTEMPTS LAST_SHRINK_TIME
--------- ------------------------ ------------- --------- ---------------------------
PRASHANT SYS_LOB0001234567C00002$ 744947712 1 14-NOV-2025 06:32:15
Note: Oracle automatically reclaimed ~710 MB of wasted LOB space.
This feature, It’s simple, it’s safe, and it saves DBAs from doing manual shrink maintenance again and again. It’s not a fast feature it’s slow and polite on purpose but it works exactly as expected.
If your system has LOBs (EBS attachments, documents, JSON, logs, media files, etc.), you should absolutely enable this. Let Oracle handle the boring part.
Hope It Helped!
Prashant Dixit
Database Architect @RENAPS
Reach us at : https://renaps.com/
Tales From A Lazy Fat DBA 