Oracle DB size grows if shrink is not performed

Products

• CA Automic Workload Automation - Automation Engine

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.