Re: Identify Temp Segments?

In article <48s2nk$kch_at_baloo.cm.co.za>, andrew_at_cm.co.za (Andrew Deighton) wrote:

>This may be a silly question, but I have not been able to find an answer in
>any of the manuals, so I figured this may be a good place to ask.
>
>Is there any way to identify which user/session has caused the creation of
>which temporary segments? We have a temporary tablespace which
>occasionally fills up, and if we can kill one job that is holding the most
>temporary space, the others could probably run to completion successfully.
>
>By looking at DBA_SEGMENTS, I can get a list of temporary segments, and
>V$SESSION gives all the current sessions, but I can't find a link between
>the two. Is there one or am I wasting my time?
>

DBA_SEGMENTS has a field for owner which will show you which user has created the temporary segment.

You could run this query:

select owner, segment_name, extents
from dba_extents
where segment_type = 'TEMPORARY'

dba_segments.owner = v$session.username

--
Glenn Stauffer

-- 
**********
Glenn Stauffer
**********