Re: Intresting Questions : How do I map the temporary segments generated due to long sort to a specific userid

From: Evan Cao <ecao_at_ti.com>
Date: Tue, 15 Dec 1998 16:29:08 -0600
Message-ID: <3676E2B4.8FB6EC83@ti.com>

Hi Arvind,

I think some post this before and some answered this post. See the attached.

BTW. can you post why or what made ask this question?

Evan

Subject:

             Re: Who is sorting
        Date:
             Wed, 09 Dec 1998 13:40:37 -0500
       From:
             Dennis Buddenborg <buddman_at_flash.net>
 Organization:
             Flashnet Communications, http://www.flash.net

 Newsgroups:
             comp.databases.oracle.server
  References:
             1

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

Arvind Gidwani wrote:

> Hi :
>
> Does any one know how to map a temporary segment to a specific oracle id
> ??
>
> Any insight would be helpful
>
> Thanks - Arvind

Received on Tue Dec 15 1998 - 16:29:08 CST