No Oracle 11g, estamos utilizando XMLELEMENTpara obter registros das tabelas. O problema parece ser, no entanto, que estamos chamando o SELECT XMLELEMENT(...).getClobVal() from DUAL;que está comendo nosso tablespace temporário como um sanduíche.
Eu li que podemos matar registros blob usando FREETEMPORARY(blob_ref), mas no formato mostrado acima, não temos referência ao blob. Existe alguma maneira de conseguir um para isso?
Como isso está sendo executado a partir de um programa e a biblioteca que usamos gera o bloco PL/SQL para nós, ele realmente não pode ser reestruturado como uma instrução PL/SQL.
O DBCP é o pool de conexão que usamos com o Tomcat e o JdbcTemplate do Spring é o que estamos usando para executar as consultas. Considerando que o JdbcTemplate troca as variáveis de ligação para nós, estamos assumindo que ele está criando um bloco anônimo nos bastidores.
A execução select * from v$temporary_lobs;revela que, para a sessão atual, ainda tenho 4 na ABSTRACT_LOBScoluna. Isso ocorre depois de executar a consulta sozinha (sem bloco anônimo) quatro vezes no SQL Developer.
Resposta originalmente deixada nos comentários por David Mann :
Se você não conseguir executar
FREETEMPORARY, acredito que sua única opção seja desconectar/reconectar a sessão. Isso causará uma limpeza de qualquer espaço de blob temporário usado por essa sessão. Tivemos um problema semelhante aqui em que não podíamos tocar no código do aplicativo, então acabamos ajustando o pool de conexões para expirar e reconectar com mais frequência.Oracle Metalink Doc ID 802897.1 "How to Release the Temp LOB Space and Avoid Hitting ORA-1652" cobre isso, para 11g sua opção é invocar
FREETEMPORARYou sair da sessão. Eles também mencionam um novo evento 60025 que deve liberar segmentos temporários quando todos os LOBs temporários de uma sessão não são mais usados, mas não vi nenhum outro detalhe sobre isso.