Re: Users and Temp segments

From: <Jared.Still_at_radisys.com>
Date: Wed, 08 May 2002 16:33:20 -0800
Message-ID: <F001.0045C43A.20020508163320@fatcity.com>

> 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

I have this script on hand if you care to give it a try.

Jared

• whotmp8i.sql
• show who is using temp space

@clears
@columns

set term off feed off
col cblocksize noprint new_value ublocksize select value cblocksize
from v$parameter
where name = 'db_block_size'
/
set term on feed on

col sid format 9999 head 'SESS|ID'
col serial_num format 999999 head 'SER #' col sort_meg format 9999.99 head 'SORT MEG'

set line 120

select

        b.username USERNAME,
        b.sid,
        b.serial# serial_num,
        b.process,
        substr(b.osuser,1,7)OS_USER,
        substr(b.  machine,1,6)M_NAME,
        substr(b.program,1,12)PROGRAM,
        su.contents,
        su.extents,
        su.blocks,
        round ( ( su.blocks * &&ublocksize ) / 1048576  , 1 ) sort_meg

from v$session b, v$sort_usage su
where b.status = 'ACTIVE'
and b.saddr = su.session_addr
order by username, sid
/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

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 - 19:33:20 CDT