Tell me more ×
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. It's 100% free, no registration required.
up vote 1 down vote favorite

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
1  
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
1  
@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
show 4 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.