Shrink DDL Supports SecureFiles LOB Segments – New in Oracle Database 20c

This blog is a bit off the topic of compression, but I think the storage savings feature discussed will be welcomed by many SecureFiles users.

Like any other file systems that manage variable-sized data, SecureFiles is subject to fragmentation during operation, including:

• Fragmentation of SecureFiles LOB Segment — Fragmentation happens when the segment largely consists of small chunks (e.g. single-block chunk in the worst case) due to chunk splitting caused by DMLs. Fragmentation leads to performance degradation and inefficiency in space usage.
• Overgrowth of SecureFiles LOB segment — A SecureFiles LOB segment can grow unnecessarily large due to concurrent DMLs, high retention setting and space preallocation (manual or automatic intended to mitigate the overhead of on-demand segment growth).

Before Oracle Database 20c, the shrink DDL (i.e. ALTER TABLE … SHRINK SPACE) did not support SecureFiles LOB segments. To release free space in SecureFile LOB segments to the tablespace, the user needed to either recreate the segment or run ‘ALTER TABLE … MOVE LOB …’, which may take a long time when the segment is large.

Now with Oracle Database 20c, the shrink DDL supports SecureFiles LOB segments (including SecureFiles LOB segments using compression, encryption and deduplication).