Oracle DB size grows if shrink is not performed

Document ID : KB000126369
Last Modified Date : 07/02/2019
Show Technical Document Details
Issue:
Even when the Automation Engine DB is properly maintained with the Archive, Reorg and Unload Utility (ucybDBAR, ucybDBRE, ucybDBUN), it is possible that the size of the DB LOB Segment keeps growing.
Environment:
Oracle
Cause:
It is a known issue that the reuse of allocated space by LOBs of type BasicFiles is not efficient.
Resolution:
To prevent the LOB segment of the DB from growing the DBA has different options:

1. Regularly reorganization of the LOB Segment

2. Upgrade to Oracle v12c and uses LOB type SecureFiles which is more efficiently reusing free space.
Within SecureFiles there  are 2 different options to handle the LOBS retention AUTO and NONE.
In very large database the option NONE may give better performance because less I/O is necessary.

Note that SecureFiles is the default in Oracle 12c but when upgrading the from 11g to 12c, the LOB type BasicFiles is retained. It may be necessary to convert the LOB type to SecureFiles.