SQLs with temp segments

From: alek <alexandru.tica_at_gmail.com>
Date: 13 Jun 2006 02:30:10 -0700
Message-ID: <1150191010.846786.306340@g10g2000cwb.googlegroups.com>

Hi,

I have a 10.2.0.1 oracle database and I want to find out all SQLs which currently generate temporary segments using v$tempseg_usage and v$sqlarea views. I used the following SQL-SELECT but the result is quite surprising to me:

SQL> select sql_text from v$sqlarea s, v$tempseg_usage t where s.ADDRESS = t.SQLADDR and s.HASH_VALUE = t.SQLHASH;

SQL_TEXT

---

select 'x' from dual
select 'x' from dual
select 'x' from dual

>From my point of view the above statements are unlikely to use temp
segments. Further searchings show that the involved session is an inactive one.

SQL> select sid, serial#, status from v$session s where s.SADDR='6030C500';

       SID SERIAL# STATUS
---------- ---------- --------
29 60334 INACTIVE The SADDR was taken from the SESSION_ADDR column of the v$tempseg_usage view.
My questions:

1.	Is it possible such simple queries to generate temp segs?
2.	Are the SQLADDR and SQLHASH the right fields to join v$tempseg with

v$sqlarea?

Many thanks. Received on Tue Jun 13 2006 - 04:30:10 CDT