DEVFYI - Developer Resource - FYI

How can one see who is using a temporary segment? (for DBA

ORACLE Interview Questions and Answers (Part 1)

(Continued from previous question...)

90. How can one see who is using a temporary segment? (for DBA

For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.
All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment. If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT
From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these examples:
select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from sys.v_$session s, sys.v_$sort_usage u
where s.addr = u.session_addr
select s.osuser, s.process, s.username, s.serial#,
Sum (u.blocks)*vp.value/1024 sort_size
from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter VP
where s.saddr = u.session_addr
and = 'db_block_size'
and s.osuser like '&1'
group by s.osuser, s.process, s.username, s.serial#, vp.value

(Continued on next question...)

Other Interview Questions