Monday, March 1, 2021

Oracle Database 12.1 -- SecureFiles LOB Space Not Reclaimed/Reused

Problem

On an Oracle Database 12.1 and below, you may encounter an issue when after inserting/deleting data from a SecureFiles LOB column the unused space is not used even after retention time passed (undo_retention) or even with explicitly set RETENTION NONE option. The used space keep growing.

 

Solution

You might be hitting the Bug 16007293 : USED_BLOCKS OF DBMS_SPACE.SPACE_USAGE FOR SECUREFILE. 
The Bug is known to be affecting 11.2.0.3 versions but the issues is also noticed on 12.1 versions. 
The bug is fixed in 12.2 version.

As a workaround you may install patch 16007293 if available for your version and platform. 
An alternative solution would be switching to BasicFiles LOB with PCTVERSION 0 (to completely disable versioning for the lob column)

No comments: