SecureFiles is the default storage mechanism for LOBs with Oracle Database, Oracle strongly recommends SecureFiles for storing and managing LOBs. The Oracle Database SecureFiles Shrink feature provides manual, and automatic methods to free the unused space in SecureFiles LOB segments and release the space back to the containing tablespace.

This blog provides an overview of both the Manual, and Automatic SecureFiles Shrink features.

About Manual SecureFiles Shrink

Use the ALTER TABLE… SHRINK SPACE statement to manually shrink a SecureFiles LOB segment. You can use the Segment Advisor, or a PL/SQL procedure such as DBMS_SPACE.SPACE_USAGE to return information about SecureFiles space usage before deciding on the SecureFiles LOB segments to shrink.

The following points are important to remember when using the manual shrink method:

  • The manual SecureFiles shrink operation is an online DDL with part of the operations being offline, where offline means concurrent DML are blocked until the shrink activity on the critical section ends. The concurrent DML statements do not fail with ORA-00054, but are blocked
  • The manual SecureFiles shrink operation disregards any flavor of undo retention and treats it as if the retention is equal to none. Users cannot expect the LOB retention feature to provide the usual guarantees after invoking the shrink operation. A user may see the ORA-1555 snapshot too old message in queries. Run the shrink operation with caution if this is a concern.

Manual Shrink can be Invoked Using these Methods:

This command targets the specified LOB column and all its partitions:
     ALTER TABLE <table_name> MODIFY LOB <lob_column> SHRINK SPACE

The following command cascades the shrink operation for all LOB columns, and its partitions in the specified table:
     ALTER TABLE <table_name> SHRINK SPACE CASCADE

Use manual shrink, on SecureFiles LOB segments, with Oracle Database release 21c and onward.

About Automatic SecureFiles Shrink

SecureFiles LOB segments can potentially become the largest consumer of database space. It may not be feasible for administrators to spend their time checking each SecureFiles LOB segment to shrink. Automatic SecureFiles Shrink uses a framework that enables automatic selection of SecureFiles LOB segments to shrink based on a set of criteria (see selection criteria below) and it runs Automatic SecureFiles Shrink in the background.

Automatic SecureFiles Shrink is designed to minimize the functional, and performance impact on concurrent workloads. While shrink runs automatically on a SecureFiles LOB segment, all Data Manipulation Language (DML) statements and Data Definition Language (DDL) statements that involve the segment will succeed. Space is gradually freed in the SecureFiles LOB segment and the performance impact is minimal.

Automatic SecureFiles Shrink does not have any effect on the BasicFiles LOBs and in-lined LOBs. Automatic SecureFiles Shrink ensures that SecureFiles LOB segments do not consume excessive free space and alleviates administrators from the burden of manually running SecureFiles Shrink.

Note that Automatic SecureFiles Shrink is not enabled by default.

In on-premise environments, run the following command to enable the Automatic SecureFiles Shrink feature:
     exec DBMS_SPACE.SECUREFILE_SHRINK_ENABLED():

In Autonomous Cloud environments, contact your system administrator to enable Automatic SecureFiles Shrink

SecureFiles LOB Segments Selection Criteria for Automatic Shrink

The Automatic SecureFiles Shrink task excludes the following SecureFiles LOB segments when choosing the SecureFiles LOB segments to shrink:

  • The SecureFiles LOB segment is not an idle segment as per LOB Segment Idle Time Limit
  • The SecureFiles LOB segment does not contain extra free space greater than the pre-allocation threshold
  • The SecureFiles LOB segment has RETENTION MAX, which means the segment keeps as many unexpired blocks as possible
  • The SecureFiles LOB segment is currently being shrunk
  • The SecureFiles LOB segment does not have enough expired free space that is no longer needed for lob retention requirement. Space that is still needed for lob retention is treated as used space
  • The SecureFiles LOB segment has failed a previous shrink task. Previous shrink attempts have failed to free space from the SecureFiles LOB segment. Automatic SecureFiles Shrink identifies the LOB segments that it failed to shrink previously and avoids such segments

Automatic SecureFiles Shrink Task

Automatic SecureFiles Shrink performs a series of steps to complete the shrink of SecureFiles LOB segments. When enabled, a shrink task is performed as one instance of the background action performed on AutoTask. The task runs every 30 minutes and performs the following steps:

  • A shrink task has 60 minutes at the start of the task. As the task progresses, it tracks both the time spent so far and the average duration of a shrink call. The latter is used to predict how long the next shrink call would take. If the time left is not enough for another call, the shrink task exits. If a shrink call goes over the 60-minute mark, it is terminated
  • Automatic SecureFiles Shrink fetches the next batch of SecureFiles LOB segments from internal catalog tables (which is ordered by objd). The last objd in the previous shrink task is used as the starting point for the next shrink task
  • Automatic SecureFiles Shrink applies the criteria filters from the Selection Criteria for SecureFiles LOB segment to remove the segments that do not qualify for the shrink task
  • Once the qualified segment is found, the shrink task can start work on the segment
  • Before starting the shrink, the shrink target is computed. The shrink target is based on the Pre-Allocation Threshold and the Automatic SecureFiles Shrink Trickle Threshold
  • Automatic SecureFiles Shrink runs the shrink command. The ALTER TABLE … SHRINK SPACE command is executed using the OCI interface
  • Automatic SecureFiles Shrink updates the timestamp for the next shrink. This timestamp indicates the earliest time when Automatic SecureFiles Shrink can select this SecureFiles LOB segment again. If space was freed successfully, the timestamp uses the current time. Otherwise, the shrink is assigned a time in the future. If shrink is not successful, a penalty time is assessed to avoid Automatic SecureFiles Shrink from selecting the same LOB segment in future shrink tasks

Key Automatic SecureFiles Shrink Capabilities

Integrates with Pre-Allocation: Automatic SecureFiles Shrink integrates with pre-allocation seamlessly without affecting performance. Automatic SecureFiles Shrink avoids the SecureFiles LOB segments that are recently pre-allocated. Segment pre-allocation is performed in the background for segments that have high demand for free space

  • Works with DDL and DML: Automatic SecureFiles Shrink targets only idle segments and skips active SecureFiles LOB segments. User driven DDL and DML statements do not fail and face minimal performance impact when Automatic SecureFiles Shrink works in the background. If Automatic SecureFiles Shrink for a SecureFiles LOB segment comes across locked rows, it skips the locked rows because locked rows are indicative of DML activity or waiting on locked rows may cause deadlocks with user transactions. Automatic SecureFiles Shrink always acquires row locks in the NOWAIT mode to avoid deadlock with user transactions
  • Targets Idle SecureFiles LOB Segments: To avoid unnecessary block accesses, Automatic SecureFiles Shrink filters SecureFiles LOB segments based on information available in System Global Area (SGA). Automatic SecureFiles Shrink selects only idle SecureFiles LOB segments and skips active SecureFiles LOB segments to minimize performance impact on active SecureFiles LOB segments
  • Covers All SecureFiles LOB Segments: Automatic SecureFiles Shrink task covers all SecureFiles LOB segments in a PDB over several intervals and this includes user created SecureFiles LOB segments and the SecureFiles LOB segments that are created using features, such as JSON and DBFS
  • Performs Shrinks in Iterations: Automatic SecureFiles Shrink does not free all the free space in the selected SecureFiles LOB segments at once. Instead, the Automatic SecureFiles shrink task frees a modest amount of space at every shrink call (iteration). The trickle threshold limit defines the amount of space to shrink in every iteration. Over time, the amount of free space in idle SecureFiles LOB segments approaches the minimum that is specified for pre-allocation
  • Executes in the Background: All steps involved in Automatic SecureFiles Shrink, including the selection of SecureFiles LOB segments to shrink, run in the background. After Automatic SecureFiles Shrink is enabled, it comes into effect with the start of a database instance. No directive regarding how Automatic SecureFiles Shrink should operate is required
  • Honors Undo Retention: Automatic SecureFiles Shrink respects the undo retention period. It does not allow a query to fail within the undo retention period because an affected SecureFiles LOB segment has been freed, relocated, or reused as a part of an Automatic SecureFiles Shrink task. Unexpired blocks are freed only after the undo retention time

More Information

Automatic SecureFiles Shrink simplifies administrator duties. Automatic SecureFiles Shrink automatically selects SecureFiles LOB segments, based on a set of criteria, and executes the shrink operation in the background for the selected SecureFiles LOB segments. With Automatic SecureFiles Shrink, the shrink operation happens transparently in small and gradual steps over time while allowing DDL and DML statements to execute concurrently. 

For more information, and additional usage details, about both Manual and Automatic SecureFiles Shrink, please see the Oracle documentation [here].