UNDO相关查询

1. undo 相关视图
dba_rollback_segs
dba_undo_extents
v$transaction
v$rollstat
v$rollname
v$undostat
x$ktuxe

2. 查看 undo 相关参数
select name,value,issys_modifiable from v$parameter where name like '%undo%';
show parameter undo

3. 查看所有 undo 表空间与数据文件
select tablespace_name,status,retention from dba_tablespaces where contents='UNDO';
select file_name,bytes from dba_data_files where tablespace_name like '%UNDO%';

4. 查看所有回滚段/区/块信息及状态信息
select * from v$rollname;
select owner,segment_name,tablespace_name from dba_rollback_segs;
select usn,xacts,rssize,hwmsize,shrinks from v$rollstat order by usn;
select extent_id,bytes,status from dba_undo_extents where segment_name='_SYSSMU1$';
select segment_name,tablespace_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name='_SYSSMU1$';
select segment_name,blocks,extents from dba_segments where segment_name='_SYSSMU1$';
select a.name,b.xacts,b.writes,b.extents from v$rollname a,v$rollstat b where a.usn=b.usn;

5. 查看事务对应的回滚信息
select xid,xidusn,xidslot,xidsqn,ubafil,ubablk from v$transaction;

Transaction ID (XID) = Undo segment No. + Slot No. + Sequence No.
undo 段头块的 trace 文件中和数据字典中均以 16 进制形式出现

事务表信息
select xid,xidusn,xidslot,xidsqn from v$transaction;
XID                  XIDUSN    XIDSLOT     XIDSQN
---------------- ---------- ---------- ----------
050022005A010000          5         34        346

转换规则
1. 050022005A010000 = 0500.2200.5A010000
2. 再做高低位互换，0500 => 0005, 2200 => 0022, 5A010000 => 0000015A
3. 获得XID = 0005.0022.0000015A, 这跟 undo header block 中记录的 xid 就完全一样

通过 sql 转换
select
    to_number(substr(xid,3,2)||substr(xid, 1,2),'xxxxxxxxxxxx') xidusn,
    to_number(substr(xid,7,2)||substr(xid, 5,2),'xxxxxxxxxxxx') xidslot,
    to_number(substr(xid,15,2)||substr(xid,13,2)||substr(xid,11,2)||substr(xid,9,2),'xxxxxxxxxxxx') xidsqn
from (select '050022005A010000' xid from dual); 

6. 查看事务表信息
select indx,ktuxesta,ktuxecfl,ktuxesqn wrap#,ktuxescnw scnw,ktuxescnb scnb,ktuxerdbf dba_file,ktuxerdbb dba_block,ktuxesiz nub from x$ktuxe where ktuxeusn = 9 and ktuxeslt <= 5;

7. 查看/转储回滚段块
select header_block,header_file from dba_segments where segment_name='_SYSSMU1$';
alter system dump undo header '_SYSSMU1$';
alter system dump datafile 5 block 4308;
select spid from v$process where addr in (select paddr from v$session where sid=(select sid from v$mystat where rownum=1));

8. 查看哪个用户正在使用回滚段的资源
select s.username,u.name from v$transaction t,v$rollstat r,v$rollname u,v$session s where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;
select a.username, b.name, c.used_ublk from v$session a, v$rollname b, v$transaction c where a.saddr=c.ses_addr and b.usn=c.xidusn and a.username='SYS';

9. 查看 UNDO 表空间统计信息
# 10 分钟自动收集一次
select to_char(begin_time,'hh24:mi:ss') begin_time,to_char(end_time,'hh24:mi:ss') end_time,undoblks from v$undostat;

10. 设置 guarantee
alter tablespace undotbs1 retention guarantee;
alter tablespace undotbs1 retention noguarantee;
select tablespace_name,retention from dba_tablespaces where contents='UNDO';

11. 查看 IMU 信息
select * from v$sysstat where name like '%IMU%';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22558114/viewspace-1122579/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22558114/viewspace-1122579/