Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> SQLs with temp segments
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 withv$sqlarea?
Many thanks. Received on Tue Jun 13 2006 - 04:30:10 CDT