查看当前回滚段表空间里是否有活动的事物:
SQL> SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk FROM v$session s,v$transaction t WHERE s.saddr=t.ses_addr;
USERNAME XIDUSN UBAFIL UBABLK USED_UBLK
------------------------------ ---------- ---------- ---------- ----------
SYS 5 2 228 1
v$transaction记录未commit的活动事务
列出这几列的含义，自己可以对比一下：
XIDUSN Undo segment number -->使用的回滚段id,可以和v$rollstat对应
UBAFIL Undo block address (UBA) filenum
UBABLK UBA block number
used_ublk --> 占用的undo block
dump出内容
SQL> alter system dump datafile 2 block 228;
udump下面看最新的那个文件，文件名包含你当前session的spid
查询当前session的process进程
select spid from v$session s,v$process p where s.paddr=p.addr and s.username='SYS';
SQL> SELECT b.name,a.xidusn, xidslot, xidsqn FROM v$transaction a, v$rollname b where a.XIDUSN = b.usn;
NAME XIDUSN XIDSLOT XIDSQN
------------------------------ ---------- ---------- ----------
_SYSSMU5$ 5 30 331
eg:
session1:
SQL> create table test as select * from dba_objects;
表已创建。
SQL> delete from test;
已删除49802行。
SQL> rollback;
回退已完成。
session2:
SQL> SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk FROM v$session s,v$transaction t WHERE s.saddr=t.ses_addr;
USERNAME XIDUSN UBAFIL UBABLK USED_UBLK
------------------------------ ---------- ---------- ---------- ----------
SYS 19 9 1068 283
SQL> SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk FROM v$session s,v$transaction t WHERE s.saddr=t.ses_addr;
未选定行
SQL> alter system dump datafile 9 block 1068;
系统已更改。
求回滚段正在处理的事务
select a.name,b.xacts,c.sid,c.serial#,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addr
and c.sql_address=d.address and c.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece;
