search cancel

LOB Segment (RT_CONTENT) for RT table growing very large

book

Article ID: 89815

calendar_today

Updated On:

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 alloacted might be more than what is actually used. This means Oracle isn't skrinking 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.