Re: Temporary LOBs in Oracle not freed up

From: Bela Vizy <bvizy_at_cox.net>
Date: Sun, 4 Apr 2004 23:59:29 -0400
Message-ID: <BQ4cc.2573$zh.48@lakeread01>

Well, the the relevant code I guess is

dbms_lob.createtemporary( clobvar, true); ....

Then the next call in the session is

dbms_lob.freetemporary(clobvar);
dbms_lob.createtemporary( clobvar, true);

The rest is just puuting together the CLOB with dbms_lob.writeappend()

What in particular do you want to know?

Thank you for helping,

Bela

***

Real-time disaster recovery
Visit http://WWW.EVERGREENASSURANCE.COM or call 410.571.6990 for information
"VC" <boston103_at_hotmail.com> wrote in message news:_jZbc.185986$po.997629_at_attbi_s52...
> Hello,
> "Bela Vizy" <bvizy_at_cox.net> wrote in message
> news:f%Ybc.1334$zh.1151_at_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

>
> Received on Sun Apr 04 2004 - 22:59:29 CDT