Re: Question related to temporary lobs

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 22 Apr 2004 10:39:55 +0100
Message-ID: <006b01c4284d$c54b5d60$7102a8c0@Primary>

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_at_espn.com> To: <oracle-l_at_freelists.org> 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

dequeue message from AQ, the payload is a CLOB declared as a type.
Create a temporary lob
assign payload to this temp_lob
message the data
pass this lob (as nocopy) to another procedure within the same package
This procedure works on the contents and completes processing
On success of this procedure execution, destroy the temporary lob
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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Apr 22 2004 - 04:36:22 CDT