In Oracle 11g, we are using
XMLELEMENT to get records from tables. The problem seems to be, however, that we are calling
SELECT XMLELEMENT(...).getClobVal() from DUAL; which is eating our temp tablespace like a sandwich.
I read that we can kill blob records using
FREETEMPORARY(blob_ref), but in the format shown above, we have no reference to the blob. Is there any way to get one for it?
Because this is being run from a program and the library we use generates the PL/SQL block for us, it really cannot be restructured as a PL/SQL statement.
The DBCP is the connection pool we use with Tomcat and Spring's JdbcTemplate is what we're using to run the queries. Considering that JdbcTemplate swaps out bind variables for us, we're assuming that it is creating an anonymous block behind the scenes.
select * from v$temporary_lobs; reveals that for the current session I still have 4 in the
ABSTRACT_LOBS column. This is after running the query by itself (no anonymous block) four times in SQL Developer.