ORACLE LOB usage

ORACLE LOBs usage.sql

http://www.grassroots-oracle.com/2015/03/listagg-to-clob-avoid-4000-chr-limit.html

https://technology.amis.nl/2015/03/13/using-an-aggregation-function-to-query-a-json-string-straight-from-sql/

-- SNIPPETS!
http://psoug.org/reference/dbms_lob.html



DEGRADACION de la sesión por creación de CLOBs internos (ABSTRACT LOBS)
==============================================================
-- si ejecutais esto vereis los SID de cada usuario conectado (session id) y los LOB (CLOB) temporales y abstractos que tiene en uso (ocupando espacio que llega un momento que degrada la sesión):
select * from v$temporary_lobs;

-- query para consultar cuántos clobs temporales tienen abiertas cada una de tus sesiones:
select s.sid,"SERIAL#","USER#",username,osuser,process,machine, status, wait_class, tb.*
FROM v$session s
    left join v$temporary_lobs tb on (tb.sid=s.sid)
where osuser = 'davidguiu'     
    -- and MACHINE='POR-ESTEBAN'
    order by MACHINE, prev_exec_start;
    
/*
"SID"                         "CACHE_LOBS"                  "NOCACHE_LOBS"                "ABSTRACT_LOBS"               "CON_ID"                      
"70"   (Raul)                 "8"                           "0"                           "0"                           "0"                           
"87"                          "119"                         "0"                           "0"                           "0"                           
"108"                         "6"                           "0"               !!!------>  "50"                          "0"                           
"118"                         "3"                           "0"                           "0"                           "0"                           
*/
select sys_context('USERENV','SID') from dual; -- para ver tu SID
select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual; -- -- para ver tu SID

Aquí hay recomendaciones como usar un tablespace aparte para los lobs, definir LOBs como CACHE si es posible (tenemos pocos así), etc.
https://docs.oracle.com/cd/A97630_01/appdev.920/a96591/adl09bes.htm

ABSTRACT_LOB: los que crea internamente de forma automática ORACLE por ejemplo al usar funciones XMLType, etc. En principio si se lanzan desde QL/SQL se limpian automaticamente pero veréis que hay 50 creados ahora mismo en la sesión de alguien (podrían ser pequeños y no tener importancia).
"If the query is executed in PLSQL, the returned temporary LOBs automatically get freed at the end of a PL/SQL program block"

Creo que los CACHED se liberan solos porque están en memoria PGA.


En concreto para el final de NOM_EXP donde usamos variable LOB para irle añadiendo partes, deberíamos crearla explícitamente  y luego liberarlo explícitamente:

Using Temporary LOBs in PL/SQL Procedure Loops.
=======================================
When repeatedly creating temporary LOBs in PL/SQL procedures in a loop, performance is improved when a PL/SQL package LOB locator variable is used inside the procedure instead of using a local variable.

This is because, by using a package variable which persists in a session, allocating extra memory to manage temporary LOBs in every procedure call is avoided.

Note:
Temporary LOBs created using a session locator are not cleaned up automatically at the end of function or procedure calls. The temporary LOB should be explicitly freed by calling DBMS_LOB.FREETEMPORARY().

CREATE OR REPLACE PACKAGE pk is
  tmplob clob;
END pk;
/
  -- instead of using a local LOB variabe, use a package variable here
  DBMS_LOB.CREATETEMPORARY(pk.tmplob, TRUE);  -- <----!!!! la creamos
  -- Aquí hacemos el CLOBdummy := CLOBdummy || 'blablabla';
  DBMS_LOB.FREETEMPORARY(pk.tmplob);    -- <-----!!!!! la liberamos explícitamente

Y el CLOBdummy:=CLOBdummy ||'texto' probablemente crea una copia entera del CLOBdummy, le añade texto y la anterior sigue ahí hasta el final (una copia anterior por cada loop!!):
---------------------
Be aware of the cost incurred in assigning temporary LOB variables. Temporary LOBs create entirely new copies of themselves on assignments. For example:
  LOCATOR1 BLOB;
  LOCATOR2 BLOB;
  DBMS_LOB.CREATETEMPORARY (LOCATOR1,TRUE,DBMS_LOB.SESSION);
  LOCATOR2 := LOCATOR;

This code causes a copy of the temporary LOB pointed to by LOCATOR1 to be created. When passing temporary LOB parameters to procedures or functions, you might also want to consider using pass by reference semantics in PL/SQL.
------------

posible SOLUCIÓN:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2957424542334
----------------------------------------------------------------------------------------------------------------------
create or replace procedure allocate_lob
as 
l_clob clob;
begin
 dbms_lob.createTemporary( l_clob, true );
 dbms_lob.open( l_clob, dbms_lob.lob_readwrite );
 
 for i in 1 .. 10
 loop
   dbms_lob.writeAppend( l_clob, 32000, rpad('*',32000,'*') );
   -- dbms_lob.writeappend(stid, length(stutable(i).stid)+1, stutable(i).stid || ',');

 end loop;
  
 dbms_lob.close( l_clob );
 dbms_lob.freeTemporary( l_clob );
end;
/

I would really not code in the style you have there -- rather, I would insert into myLob_Tbl and empty_clob -- returning that into a local variable -- and then using dbms_lob.copy / writeAppend to add to it. There shouldn't be a temporary clob here.

But, if there was, you would want to create temporary, and free it when you are done.
----------------------------------------------------------------------------------------------------------------------