TEMPORARY LOBS are not freed up automatically after PL/SQL block execution

.........[@more@]

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Connected as test

SQL>
SQL> select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as Space,tablespace,segtype,sql_text from v$sort_usage su,v$parameter p,v$session se,v$sql s
2 where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;

USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXT
------------------------------ ---------- ---------- ---------- ------------------------------- --------- --------------------------------------------------------------------------------

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

PL/SQL procedure successfully completed

SQL>
SQL> select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value)) as Space,tablespace,segtype,sql_text from v$sort_usage su,v$parameter p,v$session se,v$sql s
2 where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid;

USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXT
------------------------------ ---------- ---------- ---------- ------------------------------- --------- --------------------------------------------------------------------------------
TEST 294 1 1048576 TEMP LOB_DATA begin :id := sys.dbms_transaction.local_transaction_id; end;

SQL> /

USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXT
------------------------------ ---------- ---------- ---------- ------------------------------- --------- --------------------------------------------------------------------------------
TEST 294 1 1048576 TEMP LOB_DATA begin :id := sys.dbms_transaction.local_transaction_id; end;

SQL> /

USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXT
------------------------------ ---------- ---------- ---------- ------------------------------- --------- --------------------------------------------------------------------------------
TEST 294 1 1048576 TEMP LOB_DATA begin :id := sys.dbms_transaction.local_transaction_id; end;

SQL> /

USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXT
------------------------------ ---------- ---------- ---------- ------------------------------- --------- --------------------------------------------------------------------------------
TEST 294 1 1048576 TEMP LOB_DATA begin :id := sys.dbms_transaction.local_transaction_id; end;

關閉此SESSION ，重新進入
SQL> /

USERNAME SID EXTENTS SPACE TABLESPACE SEGTYPE SQL_TEXT
------------------------------ ---------- ---------- ---------- ------------------------------- --------- --------------------------------------------------------------------------------

SQL>

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.

Fix
~~~~
To workaround the problem, disconnect the database user session.