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%';

UNDO相關查詢

相關文章