Thursday, August 14, 2008

ORACLE- Who is using TEMP and UNDO

Execute the following query to determine who is using a UNDO Segment:

SELECT TO_CHAR(s.sid)', 'TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'
/

Execute the following query to determine who is using a TEMP Segment:

SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2) 'M' "SIZE", a.sid', 'a.serial# SID_SERIAL, a.username, a.program FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks
/

No comments:

Post a Comment