oracle监测临时表空间,监控SQL对临时表空间的使用

select to_number(decode(sid, 65535, null, sid)) sid,

operation_type operation,trunc(expected_size/1024) esize,

trunc(actual_mem_used/1024) mem, trunc(max_mem_used/1024) "max mem",

number_passes pass, trunc(tempseg_size/1024) tsize

from v$sql_workarea_active

order by 1,2;

select a.username,

a.sid,

a.serial#,

a.username,

b.blocks * 8 size_kb,

c.sql_id,

c.sql_text

from v$session a, v$tempseg_usage b, v$sqlarea c

where a.saddr = b.session_addr

and c.address = a.sql_address

and c.hash_value = a.sql_hash_value

order by b.tablespace,b.blocks;