Re: Who is sorting

From: Dennis Buddenborg <buddman_at_flash.net>
Date: Wed, 09 Dec 1998 13:40:37 -0500
Message-ID: <366EC425.FAFE7008@flash.net>

Bass, Below is a script that I downloaded from www.revealnet.com's Pipeline. I've modified it a little to suit me. Hope it helps you, also.

Dennis Buddenborg
OCP DBA
VALEO ELECTRICAL SYSTEMS #FILE: tempwho.sql
col filename format a20
col sql_text format a65 wrapped
SELECT
w.sid session_id

,s.username username
,s.osuser osuser
,w.event wait_event
,a.sql_text

FROM
v$session_wait w

,v$session s
,sys.v_$sqlarea a
,v$datafile f
,sys.file$ ff
,sys.user$ u

WHERE
u.tempts# = ff.ts#
AND ff.file# = f.file#

AND w.p1text = 'file#'
AND w.sid = s.sid
AND s.user# = u.user#
AND w.event like 'db file%'
AND a.address(+) = s.sql_address

/

Bass Chorng wrote:

> Does anybody know if there is a way to find out who
> is taking up the temporary segments for sorting ?
>
> We have had several experiences that temporary tablespace
> got used up. But all you can find out from dba_segments
> are the temp segment ID and owner SYS. I really like to
> locate the SID associated with that.
>
> Thanks for any advice in advance.
>
> -Bass Chorng
> DBA
> Lucent Technologies
Received on Wed Dec 09 1998 - 12:40:37 CST