Oracle DB size grows if shrink is not performed

Article Id: 126369

Status: Published

Updated On: 07-02-2019 10:27

Products:

CA Automic Workload Automation - Automation Engine , AUTOMIC WORKLOAD AUTOMATION

Issue/Introduction:

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.

Cause:

It is a known issue that the reuse of allocated space by LOBs of type BasicFiles is not efficient.

Environment:

Oracle

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.