Unable to create new extents in temporary tbs

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.

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 - 07:39:53 CST