A similar question came up on the newsgroup about 3 weeks ago under the title Temporary LOBs in Oracle not freed up The last comment from on it from the originator was: <<quote>> I went to Metalink to search for clues and I found people complaining about the same thing. They use OLE DB instead of JDBC but they do exactly the same (create XML in stored proc and send it back in temp CLOBS). It sounds like this is a confirmed oracle bug. The workaround they suggested was to send the data up with a clob locator attached to a physical clob (i.e. create temp or permanent table with a clob column). I will try it but this will be significantky slower then using cashed temp clobs. <<end quote>> Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar July 2004 USA West Coast, Optimising Oracle Seminar August 2004 Charlotte NC, Optimising Oracle Seminar September 2004 USA East Coast, Optimising Oracle Seminar September2004 UK - Optimising Oracle Seminar ----- Original Message ----- From: "Jamadagni, Rajendra" <Rajendra.Jamadagni@xxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Wednesday, April 21, 2004 5:08 PM Subject: Question related to temporary lobs I have this procedure that is constantly running and works with LOBS. Although there is a temporary lob creation/destroy code, I see that every time this procedure finishes a loop, the value of temporary lobs attached to this sid gets incremented by 4 (in v$temporary_lobs). Is there any way we could trace at what stage lobs get created and why they don't get destroyed? The program pseudo code is as follows ... While 1=1 Loop 1. dequeue message from AQ, the payload is a CLOB declared as a type. 2. Create a temporary lob 3. assign payload to this temp_lob 4. message the data 5. pass this lob (as nocopy) to another procedure within the same package 6. This procedure works on the contents and completes processing 7. On success of this procedure execution, destroy the temporary lob 8. loop back. End loop; TIA Raj ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------