Use the ALTER TABLE ... SHRINK SPACE statement to manually shrink a SecureFiles LOB segment. You can also use tools, such as 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 when opting for 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-54, 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. The user cannot expect the LOB retention feature to provide the usual guarantees after invoking the shrink operation. The user may see the ORA-1555 snapshot too old error message in queries. Run the shrink operation with caution if this is a concern.

SecureFiles LOB segments can potentially become the largest consumer of space in a database. 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 criteria 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 DML and 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 SecureFiles LOBs.

Automatic SecureFiles Shrink has the following features:

This section discusses how the Auto Shrink feature treats different flavors of undo retention.

As part of the Automatic SecureFiles Shrink feature, used blocks in an extent are relocated, and the extents are freed to the tablespace. After an extent is freed to the tablespace, it may be reused and overwritten. Freeing and reusing an extent during shrink can change the expected behavior of undo retention.

Automatic SecureFiles Shrink moves data in a SecureFiles LOB segment to create free space. The feature honors the LOB retention setting by not reusing space until the minimum retention requirement has been met. In practice, concurrent queries may run into ORA-1555 Snapshots too old more often compared to the same queries on an idle segment. Automatic SecureFiles Shrink for Autonomous Database skips SecureFiles LOB segments configured with the RETENTION parameter set to MAX.

Automatic SecureFiles Shrink affects out-of-line SecureFile LOBs. Automatic SecureFiles Shrink does not have any effect on the BasicFiles LOBs and inline SecureFiles LOBs.

• In on-premises environments, run the following command to enable the Automatic SecureFiles Shrink feature.

  exec DBMS_SPACE.SECUREFILE_SHRINK_ENABLE();

By default, the Automatic SecureFiles Shrink feature is disabled.

• In on-premises environments, run the following command to disable the Automatic SecureFiles Shrink feature.

  exec DBMS_SPACE.SECUREFILE_SHRINK_DISABLE();

Automatic SecureFiles Shrink follows a set of targets and limits to operate optimally.

• Automatic SecureFiles Shrink Targets
  An Automatic SecureFiles Shrink target determines the amount of space that needs to be freed in one automatic shrink run.
• LOB Segment Idle Time Limit
  The LOB segment idle time limit is the minimum time limit a LOB segment can be idle before a shrink can start.

Here are the criteria that automatic SecureFiles shrink uses for selecting SecureFiles LOB segments to 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 calls 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 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:

1. 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.
2. Automatic SecureFiles Shrink fetches the next batch of SecureFiles LOB segments from internal catalog tables (which is ordered by the object identifier). The last object identifier in the previous shrink task is used as the starting point for the next shrink task.
3. 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.

   See Also:

   Selection Criteria for SecureFiles LOB Segments to Shrink

4. Once the qualified segment is found, the shrink task can start work on the segment.
5. 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.
6. Automatic SecureFiles Shrink runs the shrink command. The ALTER TABLE ... SHRINK SPACE command is executed using the OCI interface.
7. 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 segment in future shrink tasks.

The most straightforward way of checking the result of Automatic SecureFiles Shrink is to compute the combined size of the SecureFiles LOB segments in the database. Alternatively, you can observe space saving at a micro level using the V$SECUREFILE_SHRINK table, which reports one row for each SecureFiles LOB segment that is shrunk.