Hi All,
I have got two questions regarding freeing the temporary lobs.
1. When I execute a sql query on a clob column and check the count in v$temporary_lobs for the current session the count shows as 1 and this keeps increasing as many times as I execute the query on clob column. how do we free the cache_lobs, that were created from sql query.
2. I ran the below piece of code and found dbms_lob.freetemporary has freed the cache_lobs for two clob variables only.
declare
V_1 number;V_2 number;V_3 clob;V_4 number;V_5 number;L_CLOB clob;L_CLOB1 clob;
V_XML XMLTYPE;v_clb clob;
begin
V_3 := 'BAC';
select xmltype(FLOW_XML) into V_XML from table_tb where rownum<=1;
v_clb := V_XML.getClobVal();
dbms_lob.createTemporary( l_clob, true );
DBMS_LOB.open( L_CLOB, DBMS_LOB.LOB_READWRITE );
SELECT AUDIT_TITLE INTO l_clob1 from table_2 where rownum <=1;
for i in 1 .. 10
loop
DBMS_LOB.WRITEAPPEND( L_CLOB, 32000, RPAD('*',32000,'*') );
end loop;
select CACHE_LOBS into V_4 from V$TEMPORARY_LOBS where sid = 263;
the above count shows 3
DBMS_LOB.FREETEMPORARY(L_CLOB1);
DBMS_LOB.FREETEMPORARY(V_3);
DBMS_LOB.close(L_CLOB);
DBMS_LOB.FREETEMPORARY(L_CLOB);
select CACHE_LOBS into V_5 from V$TEMPORARY_LOBS where sid = 263;
the above query count shows 1.
when i comment out V_3 := 'BAC', count shows 0. what i am doing wrong in freeing the v_3 variable.
end;
could someone please help me out