Oracle-L: RE: Users and Temp segments

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Wed, 08 May 2002 15:39:09 -0800
Message-ID: <F001.0045C399.20020508153909@fatcity.com>

Dick,

Does v$sort_usage do what you are after or is there more to your need?

Also, the following 2 script fragments are code I have saved previously:
-- below from Metalink forum article "Re : find out which sql or user is using the temporary segment now "
-- Shows all SQL being executed that is in the temp tablespace.

select distinct a.sid, a.process, a.serial#,

to_char(a.logon_time,'YYYYMMDD HH24:MI:SS') logon, a.osuser, tablespace, b.sql_text
from v$session a, v$sql b, v$sort_usage c where a.sql_address = b.address(+)

and a.sql_address = c.sqladdr;

below from Oracle-L list message by Winnie Liu entitled "Re: How do I find what users are using TEMP tablespace"
only shows each user once.

SELECT s.username,

      s.osuser,
     ds.segment_name,
     su.tablespace,
      su.extents,
     su.blocks
FROM dba_segments ds,
     v$session s,
     v$sort_usage su

WHERE su.session_addr = s.saddr
AND su.segrfno# = ds.relative_fno
;

Note that the 2nd used to work for us on 815 but now we are on 817 & using tempfile / temp tables / LMT / ? it seems to be missing something and returns too many rows.

HTH,
Bruce Reardon

-----Original Message-----
Sent: Thursday, 9 May 2002 0:58
To ALL,

I know this is an OLD topic, but at the time I was not having a problem so I let matters pass. But times change. Therefore, does anyone remember how to tie a temporary tablespace segment back to a user session?? Dick Goulet
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Reardon, Bruce (CALBBAY)
INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed May 08 2002 - 18:39:09 CDT