Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.glorb.com!wn52feed!worldnet.att.net!attbi_s52.POSTED!not-for-mail From: "VC" Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,weblogic.developer.interest.jdbc References: Subject: Re: Temporary LOBs in Oracle not freed up Lines: 45 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Message-ID: <_jZbc.185986$po.997629@attbi_s52> NNTP-Posting-Host: 24.34.72.170 X-Complaints-To: abuse@comcast.net X-Trace: attbi_s52 1081106810 24.34.72.170 (Sun, 04 Apr 2004 19:26:50 GMT) NNTP-Posting-Date: Sun, 04 Apr 2004 19:26:50 GMT Organization: Comcast Online Date: Sun, 04 Apr 2004 19:26:50 GMT Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:137404 comp.databases.oracle.server:258901 Hello, "Bela Vizy" wrote in message news:f%Ybc.1334$zh.1151@lakeread01... > Hi, > > I'm not sure if it's a JDBC problem, but could be. I guess is more of an > Oracle problem, but anyway somebody may have seen this. > > The environment is Oracle 8.1.7.4 , Weblogic 7, oracle thin driver, Solaris. > > I have a pl/sql package in Oracle, with a function which generates XML and > returns > it in a CLOB variable. The function is called through a weblogic connection > pool so > the session never closes. The CLOB in the pl/sql code is a temporary clob > with session lifespan. > > The problem is that the DB temporary tablespace is growing because one temp > lob is allocated > every time when the function is called and it is not released. First I tried > to allocate the temp clob in the pl/sql code every time when the function is > called and free it when it is called next time > (it is a package variable). Then I tried to create one for the session and > reuse it. In both cases the temp clobs are piling up. > > The temp space gets released (temp clobs disappear) only when the session is > closed, so we > "bounce" the connection pool once a day. This closes and reopens all the > connections. > > Any pointer would be greatly appreciated. > > Thank you, Pls. post your function. VC