如何查看Undo和Temp表空间中的会话

查看谁占用了Undo 表空间：
select r.name "回滚段名",
rssize/1024/1024/1024 "rssize(GB)",
s.sid,
s.serial#,
s.username,
s.status,
s.sql_hash_value,
s.sql_address,
s.machine,
s.module,
substr(s.program,1,78) "操作程序",
r.usn,
hwmsize/1024/1024/1024,
shrinks,
xacts
from    sys.v_$session s,
sys.v_$transaction t,
sys.v_$rollname r,
v$rollstat rs
where   t.addr = s.taddr
and t.xidusn = r.usn
and r.usn = rs.usn
order by rssize desc;


查看谁占用了Temp表空间：
select t.blocks*8/1024/1024 ,--一般Block Size为8K
s.username,
s.schemaname,
t.tablespace,
t.segtype,
t.extents,
s.program,
s.osuser,
s.terminal,
s.sid,
s.serial#
from v$sort_usage t,v$session s
where t.session_addr = s.saddr;


查询Temp表空间中具体的SQL：
select  sql.sql_id,
t.blocks*8/1024/1024 ,
s.username,
s.schemaname,
t.tablespace,
t.segtype,
t.extents,
s.program,
s.osuser,
s.terminal,
s.sid,
s.serial#，
sql.sql_text
from v$sort_usage t,v$session s,v$sql sql
where t.session_addr = s.saddr
and t.sqladdr = sql.address
and t.sqlhash = sql.hash_value;