title: How to workaround a memory leak in Oracle
body: <p>I have jobs in Oracle that can run for hours doing a lot of calculations involving (but not limited to) XmlTransform. I have noticed that the PGA memory is increasing (and the performance degrading) gradually until at some point the job fails with an out of memory (PGA) message. We have applied some fixes, but they don't seem to solve the issue.
  Stopping the jobs and restarting them, solves my issue, the performance is good again and the memory is low...
  All the code is written in PL/SQL and SQL.</p>
  
  <p>Question:
  As I want to solve this as soon as possible, I was wondering how I can workaround this type of issue in Oracle.</p>
  
  <p>My main thinking goes to somehow:</p>
  
  <ul>
  <li>restarting the job after some time (possibly the most simple solution) using Advanced Queuing</li>
  <li>restart the current session? </li>
  <li>executing some code syncronously in another session, maybe another job.</li>
  </ul>
  
  <p>Oracle 12.1.0.2</p>
  
  <p>EDIT: As asked here's sample code with XMLTransform:</p>
  
  <pre><code>function i_Convert_Xml_To_Clob (p_Zoek_Result_Type_Id in Zoek_Result_Type.Zoek_Result_Type_Id%type,
                                  p_Xml                 in xmltype,
                                  p_Xml_Transformation  in xmltype) return clob is
    mResult clob;
  begin
    if p_Xml_Transformation is not null then
      select Xmltransform (p_Xml, p_Xml_Transformation).getclobval()
        into mResult
      from Dual;
    elsif p_Xml is not null then
      mResult := p_Xml.getclobval();
    else
      mResult := null;
    end if;
  
    return mResult;
  end i_Convert_Xml_To_Clob; 
  </code></pre>