Re: HELP: how to clean up temporary lobs

In article <a5ae1554.0112011320.4165d024_at_posting.google.com>, "Ed Wong" <ewong_at_mail.com> wrote:

> We have a c + oci software running 24*7 on a database doing batch
> processing. One of the things it does is insert/select huge amount of
> blobs. I noticed it keep filling up the temporary tablespace(about 3 gb
> temp space usage a day). The temp lobs never get clean up unless I
> restart the software(session). Is there any way to clean up the
> temporary lobs of a particular session without terminating it?
>
> I can only bounce the instance once a week in weekend. And that
> particular software needs to run 24 hours a day without termination.
> Thanks.
>
> ewong
Temporary segments can be reused. That is precisely the meaning of the designation 'TEMPORARY'. Take a look into the v$sort_usage to see how much actual space is being used. Unfortunately, there isn't a view like dba_free_temp_space which would tell you how much tem,porary space you have left, but based on v$sort_usage and the actual size of your tempfiles, you can calculate that rather easily.

-- 
Mladen Gogala