Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

closed as off-topic by Max Vernon, RolandoMySQLDBA, Phil, Erik, swasheck Mar 24 at 16:41

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, RolandoMySQLDBA, Phil, Erik, swasheck
If this question can be reworded to fit the rules in the help center, please edit the question.

I'm really not an expert on LOBs but it looks to me like you are barking up the wrong tree - FREETEMPORARY is surely only for LOBs created with CREATETEMPORARY? How big are your LOBs and what evidence do you have that they are 'eating' your temp tablespace? – Jack Douglas Jan 9 '12 at 14:19
In addition to Jack's questions, if you have a SELECT statement in a PL/SQL block, you must by definition be selecting the result into some local variable. If you have no reference to the CLOB, that implies that you are issuing a SQL statement, not running a PL/SQL block, and that the calling application has a cursor handle for fetching the CLOB data that needs to be closed once you've fetched all the data. – Justin Cave Jan 9 '12 at 14:42
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. – David Mann Jan 9 '12 at 14:45
@DavidMann So you're saying that killing the session will cause the temp space to clear up? And if so, is each connection from a Tomcat DBCP considered a separate session? – Andy Jan 9 '12 at 16:41
@Andy I found the reference - 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. – David Mann Jan 10 '12 at 15:08

Browse other questions tagged or ask your own question.