Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: tempspace usage

Re: tempspace usage

From: jame tong <>
Date: Wed, 20 Apr 2005 11:37:14 +0800
Message-ID: <>

some other actions will also need temporary tablespace. check it further. hash join/ global temporary table/

some operations than will need sort also will possible make use of temporary tablespace.
sort/group/distinct/union/merge join/... etc

On 4/20/05, Steve Rospo <> wrote:
> A 10046 trace should show direct path reads/writes when the session start=
> sorting/hashing to disk. Also check the SEGTYPE column to see if the
> space is being used for sort, hash or temporary tables/indexes.
> If the problem is the space doesn't disappear from V$SORT_USAGE across
> multiple statements, I'd bet it was temp tables/indexes because a sort or
> hash should disappear when the statement ends while the temp table
> persists until the end of the transaction or session depending on how its
> scope was declared. The join to V$SQLAREA as has been suggested won't
> help in this case because the V$SORT_USAGE.SQLADDR doesn't point anywhere
> since no single SQL statement "owns" it. I've never been able to
> effectively been able to map such a row back to a particular temp
> table/index just using the data in V$SORT_USAGE.
> If there's only one statement and it keeps running and running, allocatin=
> space the entire time, it's the culprit. There's lots of reasons for
> this: Massive hash join, ORDER BY on a massive result set (like a
> cartesian product), really big GROUP BY, etc.
> S-
> On Tue, 19 Apr 2005, Greg Norris wrote:
> > Is there a way to determine (or trace) which individual statements are
> > causing tempspace to be allocated to a session? I've got a databases
> > where half a dozen sessions seem to gradually allocate more and more
> > tempspace (as measured by v$sort_usage), and then never release it.=3D2=
> > Eventually they acquire almost all of the available space, which of
> > course causes a flurry of ORA-1652 errors from other sessions.
> >
> > At the moment, I'm examining a trace from one of these sessions. So
> > far, I don't see anything which could cause this behaviour... a few
> > inserts, some relatively simple selects, nothing particularly complex.
> > Any suggestions on how to troubleshoot this sort of issue?
> >
> > The DB in question is Oracle (32-bit), running on Solaris 8.
> --
> Stephen Rospo Principal Software Architect
> Vallent Corporation (formerly Watchmark-Comnitel)
> (425)564-8145
> This email may contain confidential information. If you received this in
> error, please notify the sender immediately by return email and delete th=
> message and any attachments. Thank you.
> --

Received on Tue Apr 19 2005 - 23:41:25 CDT

Original text of this message