Here is a sorter script that I have in my archive:
select t1.tablespace
, sum( t1.blocks * to_number( t3.value ) ) / 1024 /
1024 mb_used
, sum( t1.extents ) tot_extents
, t2.username
, t2.osuser
, t2.SID ||','|| t2.serial# SID_PID
, t4.spid ,
t5.sql_text,
t1.segtype
from v$sort_usage t1
, v$session t2
, v$parameter t3
, v$process t4 ,
v$sqlarea t5
where t1.SESSION_ADDR = t2.SADDR
and t3.name = 'db_block_size'
and (t2.PROCESS=t4.SPID or t2.paddr = t4.addr)
and t2.sql_address=t5.address
and t2.status = 'ACTIVE'
group by
t1.tablespace
, t2.username
, t2.osuser
, t2.machine
, t2.schemaname
, t2.program
, t2.SID ||','|| t2.serial#
, t4.spid,
t5.sql_text
,t1.segtype
/
hth,
jack
- "Reardon, Bruce (CALBBAY)"
<Bruce.Reardon_at_comalco.riotinto.com.au> wrote:
> 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).
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: jack_silvey_at_yahoo.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 - 22:03:20 CDT
