- 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
转换规则
- 050022005A010000 = 0500.2200.5A010000
- 再做高低位互换,0500 => 0005, 2200 => 0022, 5A010000 => 0000015A
- 获得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
′
;
a
l
t
e
r
s
y
s
t
e
m
d
u
m
p
u
n
d
o
h
e
a
d
e
r
S
′
Y
S
S
M
U
1
'; alter system dump undo header '_SYSSMU1
′;altersystemdumpundoheaderS′YSSMU1’;
alter system dump datafile 5 block 4308;
select spid from v
p
r
o
c
e
s
s
w
h
e
r
e
a
d
d
r
i
n
(
s
e
l
e
c
t
p
a
d
d
r
f
r
o
m
v
process where addr in (select paddr from v
processwhereaddrin(selectpaddrfromvsession where sid=(select sid from v$mystat where rownum=1));
8.查看哪个用户正在使用回滚段的资源
select s.username,u.name from v
t
r
a
n
s
a
c
t
i
o
n
t
,
v
transaction t,v
transactiont,vrollstat r,v
r
o
l
l
n
a
m
e
u
,
v
rollname u,v
rollnameu,vsession 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
s
e
s
s
i
o
n
a
,
v
session a, v
sessiona,vrollname 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%’;