Re: Unable to create new extents in temporary tbs

"Howard J. Rogers" <hjr_at_dizwell.com> a écrit dans le message news: 3fc6b855$0$20496$afc38c87_at_news.optusnet.com.au...
>
> "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
> >
> >
> >
>
>

Hi Howard,
Thanks for your answer.
Yesterday, I investigated a little more after posting (this I should have done *before* posting ;)
and I noticed that, indeed, the segment was kept within the tbs with all its extents allocated but not marked free
as I first supposed but *used*.
How can temp extents can be marked use although there is *no* sort activity at all (actually no activity of any kind)

TIA
Alkos Received on Fri Nov 28 2003 - 02:13:16 CST