Re: Temporary LOBs in Oracle not freed up

Mark,

I tried to make the tablespace locally managed, but it doesn't help. But this helped in another way: If we missed to reopen the pool connections to clear up the temp segments, occupied by the temp clobs, and the temp tablespace grew to 10G+ with small segments (it had to be small, because oracle allocated one segment per temp clob!) it was a real pain to drop and recreate the tablespace when it was dictionary managed (it took 1 day to drop).

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.

I'll keep you posted,

Thanks for the help,

Bela

simmons_mark_at_yahoo.com (Mark) wrote in message news:<5366fb41.0404051019.7b17e2e3_at_posting.google.com>...
> 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 Tue Apr 06 2004 - 13:49:58 CDT