Take the 2-minute tour ×
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.

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

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.