Re: v$process_memory_detail: Category=Other, HEAP_NAME = kolaslAssign

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Thu, 25 Feb 2016 00:28:00 +0300
Message-ID: <CAOVevU70pSfKdOzYSO8osMmQsqewbtecJ7c=MFkcL6eHmCxQFg_at_mail.gmail.com>

Hi Stefan,

Thanks for confirming my guess!
Unfortunately, this issue is on windows server. And I can't to create a test case that will have the same behaviour like buggy query, because my test with the same function shows that memory is freeing normally.

Source code is very simple:

create type t_objstrset as table of varchar2(4000);
/

create or replace function func_collection_to_clob (

   p_collection t_objstrset
  ,p_delim varchar2:=','
  )
return clob
as

   res clob;
   tmp varchar2(32767);
   elm varchar2(4000);
begin

   if p_collection is null then

      res:=null;
   else

      for i in 1..p_collection.count loop
         elm:=p_collection(i);
         if length(tmp)+length(elm)>=32767 then
            res:=res||tmp;
            tmp:=null;
         end if;
         tmp:=tmp||p_delim||elm;
      end loop;
      res:=res||tmp;

   end if;
   return substr(res,2);
end;
/

I've tried also to create lobs with dbms_lob.createtemporary(res ,true, dbms_lob.call);
But without success:

create or replace function func_collection_to_clob (

   p_collection t_objstrset
  ,p_delim varchar2:=','
  )
return clob
as

   res clob;
   tmp varchar2(32767);
   elm varchar2(4000);
begin

   if p_collection is null then

      res:=null;
   else

      dbms_lob.createtemporary(res ,true, dbms_lob.call);

      for i in 1..p_collection.count loop
         elm:=p_collection(i);
         if length(tmp)+length(elm)>=32767 then
            res:=res||tmp;
            tmp:=null;
         end if;
         tmp:=tmp||p_delim||elm;
      end loop;
      res:=res||tmp;

   end if;
   res:=substr(res,2);
   return res;
end;
/

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 24 2016 - 22:28:00 CET