如何檢視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;