Re: Unable to create new extents in temporary tbs

"Alkos" <azerty_at_nospam.org> wrote in message news:bq4ura$ilj1_at_news.rd.francetelecom.fr...
> Hello,
>
> A application operates an Oracle 8.1.7.4 DB on a SUN Solaris box, using it
> as a repository (only select)
> and log 'file' (insert mode). That DB doesn't have any human user only
> webMethods stuff
> accessing it.
>
> Since this morning, the TMP tablespace (temporary LMT) has run out of
space
> (75 M, one tempfile)
>
> I queried all possible V$ views about sort usage, tmp extents and so on
and
> discover that
> a single segment got all the space allocated. No sort usage found (view is
> empty)
> No transaction still executing
> This situation even remained when I disconnect all sessions (this is an
> integration DB)
>
> I decided to add a second file of 75M.
> It succeeded and then, when I looked to the %free it was 0% in both files.

It's supposed to be zero. That's the way temporary tablespaces work.

You startup the instance.
You perform a sort.
Your session creates temporary segments within the temporary tablespace, and if needed use up all the space.
Your report ends.
The tempory segments are *not* dropped (that's what makes this a proper temporary tablespace)
I start a sort.
I *reuse* the temporary segments you've already created.

So once the space has been allocated, it remains allocated for the lifetime of the instance.
A shutdown-startup will cause SMON to clear the temporary segments (but is not actually needed, because of the continual re-use of the temporary segments feature).

Regards
HJR
>
> Then (one hour later) I dropped the file I'd just added (there is almost
no
> activuty on this DB).
> Everything was still ok so I went for lunch ;-)
> Now, I'm back and the situation is the same as before, a single sort
segment
> occupying all the space in TMP
> and no further allocation allowed by Oracle.
>
> I think I understood the way temp tbs were used, maybe I missed something
> :((
>
> Can anyone tell me
> why so many extents remain allocated ?
> is there a way to find out who (user, query) allocated them ?
>
> thank in advance
> Alkos
>
>
>
Received on Thu Nov 27 2003 - 20:51:38 CST