Oracle Undo查看

--******Undo占用查看*****--
--Start
--查询SQL
SELECT s.serial#,
       s.sid,
       s.username 用户名,
       t.used_ublk * 8192 / 1024 / 1024 || 'M' 使用大小,
       substr(s.program, 1, 78) 操作程序,
       e.sql_text
  FROM gv$session s, gv$transaction t, gv$sqlarea e
 WHERE t.addr = s.taddr
   AND username = 'xxx'
   AND t.inst_id = s.inst_id
   AND e.address(+) = decode(s.sql_hash_value, 0, s.prev_sql_addr, s.sql_address)
 ORDER BY t.used_ublk DESC;
--不查询SQL
SELECT s.serial#,
       s.sid,
       s.username,
       used_ublk,
       t.used_ublk * 8192 / 1024 / 1024 || 'M' "Mb",
       substr(s.program, 1, 78),
       prev_exec_start,
       t.start_date,
       s.sql_id
  FROM gv$session s, gv$transaction t
 WHERE t.ses_addr = s.saddr
   AND t.inst_id = s.inst_id
 ORDER BY t.used_ublk DESC;
-- End

--大事务恢复进展
--displays information about the progress of the transactions that Oracle is recovering.
SELECT * FROM v$fast_start_transactions

--session被kill时看不到，占用量用这个SQL看整体的使用量
SELECT d.tablespace_name,
       d.space "TOTAL_SPACE(M)",
       u.u_space "USED_SPACE(M)",
       round((u.u_space / d.space) * 100, 2) "USED_RATE(%)"
  FROM (SELECT tablespace_name, round(SUM(bytes) / (1024 * 1024), 2) space
          FROM dba_data_files
         GROUP BY tablespace_name) d,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 u_space
          FROM dba_undo_extents
         WHERE status = 'ACTIVE'
         GROUP BY tablespace_name) u
 WHERE d.tablespace_name = u.tablespace_name;

--超大回滚事务查看
--********Begin*********
--查找回滚进展
SELECT round(undoblocksdone / cputime) avg_rate,
       SYSDATE - cputime / 3600 / 24 start_rollback_time,
       undoblockstotal - undoblocksdone todo,
       SYSDATE + (undoblockstotal - undoblocksdone) / undoblocksdone * cputime / 3600 / 24 end_time,
       t.*,
       SYSDATE
  FROM gv$fast_start_transactions t
 WHERE undoblocksdone <> undoblockstotal
--and xid = '59000100299E2F02' 
 ORDER BY usn;

-- 通过Start_rollback_time找对应的更早一些的快照
SELECT to_char(t.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS'), t.* FROM dba_hist_ash_snapshot t;
--在通过snap_id、dbid、instance_number、Xid找是那个Schema下的回滚
SELECT *
  FROM dba_hist_active_sess_history t
 WHERE t.snap_id = 82980
   AND t.dbid = 867060512
   AND t.instance_number = 1
   AND xid = '59000100299E2F02';
--********End*********

http://blog.itpub.net/18922393/viewspace-707946/
--事务标识(xid)解析 
--解析XID 转换为usn,slot,sqn
WITH v AS
 (SELECT '37060700D5CC5500' xid FROM dual)
SELECT to_number(substr(v.xid, 3, 2) || substr(v.xid, 1, 2), 'xxxx') usn,
       to_number(substr(v.xid, 7, 2) || substr(v.xid, 5, 2), 'xxxx') slot,
       to_number(substr(v.xid, 15, 2) || substr(v.xid, 13, 2) || substr(v.xid, 11, 2) ||
                 substr(v.xid, 9, 2),
                 'xxxxxxxxxx') sqn,
       ---transaction 等待事件的信息,P1TEXT undo seg#|slot#   P2TEXT wrap#
       to_number(substr(v.xid, 3, 2) || substr(v.xid, 1, 2) || substr(v.xid, 7, 2) ||
                 substr(v.xid, 5, 2),
                 'xxxxxxxxxxxxx') p1,
       to_number(substr(v.xid, 15, 2) || substr(v.xid, 13, 2) || substr(v.xid, 11, 2) ||
                 substr(v.xid, 9, 2),
                 'xxxxxxxxxx') p2,
       substr(v.xid, 3, 2) || substr(v.xid, 1, 2) || substr(v.xid, 7, 2) || substr(v.xid, 5, 2) p1raw,
       substr(v.xid, 15, 2) || substr(v.xid, 13, 2) || substr(v.xid, 11, 2) || substr(v.xid, 9, 2) p2raw
  FROM v;
---Transaction等待事件p2\p3 转化为XID
WITH v AS
 (SELECT '0000000006370007' p2raw, '000000000055CCD5' p3raw FROM dual)
SELECT substr(p1raw, 11, 2) || substr(p1raw, 9, 2) || substr(p1raw, 15, 2) || substr(p1raw, 13, 2) ||
       substr(p2raw, 15, 2) || substr(p2raw, 13, 2) || substr(p2raw, 11, 2) || substr(p2raw, 9, 2) xid
  FROM v;