LOB Segment (RT_CONTENT) for RT table growing very large

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

LOB Segment (RT_CONTENT) for RT table growing very large

Environment

Release: AOATAM99000-9.0-Automic-One Automation Tools-Application Manager
Component:

Resolution

Detailed Description and Symptoms

Even with regular DB Maintenance, the LOB segment for the RT_CONTENT column of the RT table is still growing.

Investigation

Space allocated might be more than what is actually used. This means Oracle isn't shrinking the space allocated.

Run as Admin:

(Finds space assigned to LOB segment for RT Table)

?SELECT owner, table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'RT';

Run as Automic Oracle User:

(Find actual space used by RT_CONTENT)

SELECT NVL((SUM(DBMS_LOB.GETLENGTH(rt_content))),0) AS BYTES
FROM RT;

Solution

If the numbers returned above are drastically different, then the LOB column contains unused space and needs to be shrunk.

Run as Automic Oracle User:

ALTER TABLE RT MODIFY LOB (RT_CONTENT) (SHRINK SPACE);

Please note that your Oracle DBA should be consulted before running this statement.