TEMPORARY LOBS are not freed up automatically after PL/SQL block execution (Doc ID 228479.1)

Last updated on JULY 14, 2023

Applies to:

Symptoms

Problem
~~~~~~~

Temporary LOBS are not freed automatically after PL/SQL block execution.

Following the execution of the following PL/SQL block, the temporary LOBs are 
not being freed up automatically.

SQL> DECLARE 
  2  a clob; 
  3  BEGIN 
  4  dbms_lob.createtemporary(a, TRUE,dbms_lob.call); 
  5  dbms_lob.freetemporary(a); 
  6  END; 
  7  /

PL/SQL procedure successfully completed.

This can be seen using the following select :-

SQL> select s.username, s.sid, u.tablespace, u.contents, u.segtype, 
  2  round(u.blocks*8192/1024/1024,2) MB 
  3  from v$session s, v$sort_usage u 
  4  where s.saddr = u.session_addr 
  5  and u.contents = 'TEMPORARY' 
  6  order by MB DESC ;

USERNAME   SID   TABLESPACE  CONTENTS     SEGTYPE    MB
--------- -----  ----------- ----------   -------    ---
SK          9     TEMP       TEMPORARY    LOB_DATA    1

The above result shows still the temporary LOB is allocated.

Changes

Cause