FREETEMPORARY on blob with no reference

bloboracle

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.

Running 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.

Best Answer

Answer originally left in comments by David Mann:

If you are unable to run FREETEMPORARY then I believe your only option is to disconnect/reconnect the session. This will cause a cleanup of any temp blob space used by that session. Had a similar issue here where we couldn't touch the app code, so we ended up tweaking their connection pool to expire and reconnect more frequently.

Oracle Metalink Doc ID 802897.1 "How to Release the Temp LOB Space and Avoid Hitting ORA-1652" covers this, for 11g your option is to invoke FREETEMPORARY or to exit session. They also mention a new event 60025 which is supposed to free temp segments when all the temp LOBs for a session are no longer used, but I didn't see any other detail on that.

Related Question