Re: Temporary LOBs in Oracle not freed up

I can't remember whether this is relevant to your version or not, but we had a problem with LOB space not being freed if you are using segment auto management. The workaround was to make sure the tablespace was manually managed. This bug was fixed in 9.2.04. Like I said, I don't remember how far back it went.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

"Bela Vizy" <bvizy_at_cox.net> wrote in message news:<BQ4cc.2573$zh.48_at_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 Mon Apr 05 2004 - 13:19:27 CDT