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
sort/group/distinct/union/merge join/... etc
On 4/20/05, Steve Rospo <srospo@(protected):
> 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.
> 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 220.127.116.11.0 (32-bit), running on Solaris 8.
> Stephen Rospo Principal Software Architect
> Vallent Corporation (formerly Watchmark-Comnitel)
> 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.