LOB Segment (RT_CONTENT) for RT table growing very large

Document ID : KB000089815
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
LOB Segment (RT_CONTENT) for RT table growing very large
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.