oracle 通过insert一条数据观察事务id和回滚段信息

--insert 操作观察undo 


create  table   t1(name varchar2(100));
insert into t1  values ('SCOTT');

select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from t1;

     FILE#     BLOCK#
---------- ----------
         7        348

--保持事物不提交，查看事务信息：

select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
         3          9        930          4       1705        199          8




--session 2

oradebug setmypid
alter system dump datafile 7 block 348;
oradebug close_trace
oradebug tracefile_name;
--/home/oracle/19c/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_11236.trc


Object id on Block? Y
 seg/obj: 0x11e52  csc:  0x0000000000224efe  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1c00158 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.009.000003a2  0x010006a9.00c7.08  ----    1  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01c0015c
data_block_dump,data header at 0x12e95e064


--xid（事务id)  由三部分组成： undo段号 + 事务表槽号 +undo段循环覆盖使用的次数
--0x0003.009.000003a2 转换成10进制是3.9.930，和v$transaction一致

--uba(回滚段地址):表示该事务在undo块上被应用的最后一条undo的地址，或者是数据块要回滚的起点
--uba 0x010006a9.00c7.08 由三部分组成：回滚块地址 + 序列号 + undo条目的记录号


--Lck=1  事务锁的行数
--scn  事务块最后提交的scn
--fsc    free  space credit ,表示未提交而不确定的空闲空间，一般由delete或者update操作产生



select to_number('10006a9','xxxxxxxxxxx') from dual;
TO_NUMBER('10006A9','XXXXXXXXXXX')
----------------------------------
                          16778921

select dbms_utility.data_block_address_file(16778921) file#,dbms_utility.data_block_address_block(16778921) block# from dual;
     FILE#     BLOCK#
---------- ----------
         4       1705



col name for a20
select * from v$rollname where usn=3;

       USN NAME                     CON_ID
---------- -------------------- ----------
         3 _SYSSMU3_2421748942$          0



--转储段头
oradebug setmypid
alter system dump undo header'_SYSSMU3_2421748942$';
oradebug close_trace
oradebug  tracefile_name
--/home/oracle/19c/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_13039.trc


--转储undo block
oradebug setmypid
alter system dump datafile  4 block 1705;
oradebug close_trace
oradebug  tracefile_name
--/home/oracle/19c/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_13254.trc


select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
         3          9        930          4       1705        199          8
		 
--UBAREC ==Rec	 
		 

UNDO BLK:
 xid: 0x0003.009.000003a2  seq: 0xc7  cnt: 0x8   irb: 0x8   icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f58     0x02 0x1ed0     0x03 0x1e6c     0x04 0x1e1c     0x05 0x1dac
0x06 0x1d5c     0x07 0x1cdc     0x08 0x1c6c

--xid 事务id
--cnt 回滚段的数量
--irb 表示回滚段的开始REC (record)

* Rec #0x8  slt: 0x09  objn: 73298(0x00011e52)  objd: 73298  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x00
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x010006a9.00c7.06 ctl max scn: 0x0000000000224798 prv tx scn: 0x00000000002247b3
txn start scn: scn: 0x0000000000224efe logon user: 106
 prev brb: 16778905 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z             
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01c0015c  hdba: 0x01c0015a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)

End dump data blocks tsn: 2 file#: 4 minblk 1705 maxblk 1705


--rci 表示下一个rec，0x00表示这是最后一个REC,咱们只做了一个insert只产生了一个undo记录，这一条就是重点
--op:  Z代表事务的回退终点

--KDO Op code 表示回退操作，这里是drop