2011-12-14

how to see undo space usage

the original link :  http://oracledisect.blogspot.com/2011/11/who-is-using-your-undo-space-improved.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+oracledbdisected+%28Oracle+Database+Disected%29

Then the script for Oracle 11g is as follows:
set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND a.statistic# = 284
AND a.value > 0
ORDER BY a.value DESC

If you want to run this script on versions 10g1 and 10g2, just replace the statistic# with 176.

Niciun comentariu:

Trimiteți un comentariu