Re: Who is using temp tablespace?

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1997/06/02
Message-ID: <3392D8B2.33C3@teldta.com>#1/1

Jonathan Lewis wrote:
>
> You could try running queries against v$session_wait for
> event like 'db file%^read'
> and p1 in (list of file ids for temp tablespace files)
>
> This is likely to give some indication of a user that is hitting the
> files a lot.
>
> EAL <asn_at_spot.Colorado.EDU> wrote in article
> <5lqpfd$mvo_at_lace.colorado.edu>...
> > Hi netters. We have big jobs sorting in the temp tablespace. Is
 there any
> > way to determine which jog or which user is using the temp
 tablespace?
> > We did query on dba_segments and could not get what we want.
> >
> > TIA.
> >

how's about.....

  1 select unique

  2         vs.process,
  3         vp.spid,
  4         vs.sid,
  5         vs.serial#,
  6         vss.statistic#,
  7         vsn.name,
  8         vss.value

  9 from v$sesstat vss, v$statname vsn, v$process vp, v$session vs  10 where vs.paddr = vp.addr
 11 and vs.sid = vss.sid

 12  and vss.statistic# = vsn.statistic#
 13  and vss.value > 0
 14  and vsn.name like '%sort%'

 15* order by vs.sid, serial#, vs.process, vp.spid, vss.statistic#

which produces (run it several times and see what's changing).....

Pgm Oracle Oracle
Unix Unix Session

Pid    Pid         ID Serial# Stat# Statistic
Name                         Value
------ ------ ------- ------- ----- ----------------------------------
---------
       17300        7      21   130 sorts
(memory)                             1
20481  19970        9      47   130 sorts
(memory)                            46
                                132 sorts
(rows)                            2227

brian.maclean_at_teldta.com Received on Mon Jun 02 1997 - 00:00:00 CDT