已索引
该文章已被其它文章引用。

set lines 200
set pages 100
col username for a10
col program for a10
col event for a10
col machine for a20
col object_name for a15
SELECT substr(vs.username,1,10) "USERNAME",
   vs.sid "SID",
   vs.serial# "SERIAL#",
   vs.sql_id "SQL_ID",
   vs.prev_sql_id "PREV_SQL_ID",
   vs.machine,
   vs.program,
   vs.blocking_session,
   vs.event,
   vt.XID "Transaction_XID",
   vt.used_urec "RECORDS",
   vt.used_ublk*p.VALUE/1024/1024 "SIZE(M)",
   do.object_name,
   vl.locked_mode  -- 3 means row-X
FROM   v$transaction vt,
       v$session vs,
       v$rollname vrn,
   v$rollstat vrb,
   v$parameter p,
   v$locked_object vl,
   dba_objects do
   WHERE vrn.usn = vrb.usn
     AND vrb.usn = vt.xidusn
     AND vs.taddr = vt.addr
     AND p.NAME = 'db_block_size'
     AND vt.XIDSQN = vl.XIDSQN
     AND vl.object_id = do.object_id
ORDER BY "SIZE(M)" desc;

建议周期性（如每5分钟）对 undo 的使用情况进行监控，将使用 undo 最多 sql_id 和 undo 量打印出来，如此，当需要查询过去某时间谁消耗了最多的 undo 时，就可以通过 zabbix 迅速找到。