oracle： 通过update一条数据观察所属数据块的xid和uba

--update  操作观察 undo 
drop   table   t1;
create table t1(id number,name varchar2(100));
insert into t1  values (1,'SCOTT');
commit;
select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from t1;

     FILE#     BLOCK#
---------- ----------
         7        364

update t1 set name='TEST' where name='SCOTT';

--保持事物不提交，查看事务信息：
select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec from v$transaction;		 


    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
         6         10        927          4       1101        202         54




--session 2
alter system checkpoint;
oradebug setmypid
alter system dump datafile 7 block 364;
oradebug close_trace
oradebug tracefile_name;
--/home/oracle/19c/diag/rdbms/ora19c/ora19c/trace/ora19c_ora_14453.trc

--dump block 的事务信息


Block header dump:  0x01c0016c
 Object id on Block? Y
 seg/obj: 0x11e54  csc:  0x0000000000225c47  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1c00168 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0002.00e.0000039b  0x01000e57.00d8.22  C---    0  scn  0x0000000000225c39
0x02   0x0006.00a.0000039f  0x0100044d.00ca.36  ----    1  fsc 0x0001.00000000



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

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


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


flag=--------
ntab=1                                       --本block只有一张表
nrow=1                                       --记录数
frre=-1
fsbo=0x14                                    --空闲空间的结束offset
fseo=0x1f81                                  --空闲空间的开始offset
avsp=0x1f78                                   --可使用的空间
tosp=0x1f79                                   --总共空余的空间
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f81
block_row_dump:
tab 0, row 0, @0x1f81
tl: 11 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 02
col  1: [ 4]  54 45 53 54                     --存放的TEST字符

SQL> select dump('TEST',16) from dual;

DUMP('TEST',16)
--------------------------------------------------
Typ=96 Len=4: 54,45,53,54




select to_number('100044d','xxxxxxxxxxx') from dual;
TO_NUMBER('100044d','XXXXXXXXXXX')
----------------------------------
                          16778317

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

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

       USN NAME                     CON_ID
---------- -------------------- ----------
         6 _SYSSMU6_813816332$          0



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

UNDO BLK:
 xid: 0x0006.00a.0000039f  seq: 0xca  cnt: 0x36  irb: 0x36  icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f98     0x02 0x1f18     0x03 0x1ea8     0x04 0x1e34     0x05 0x1d88
0x06 0x1ce8     0x07 0x1c60     0x08 0x1bdc     0x09 0x1b88     0x0a 0x1b10
0x0b 0x1a88     0x0c 0x1a04     0x0d 0x19b0     0x0e 0x1938     0x0f 0x18b0
0x10 0x182c     0x11 0x17d8     0x12 0x1760     0x13 0x16d8     0x14 0x1674
0x15 0x1630     0x16 0x15dc     0x17 0x1530     0x18 0x14b8     0x19 0x1450
0x1a 0x13a4     0x1b 0x133c     0x1c 0x12d4     0x1d 0x124c     0x1e 0x11d8
0x1f 0x1138     0x20 0x10b0     0x21 0x103c     0x22 0x0fb4     0x23 0x0f54
0x24 0x0ec8     0x25 0x0e68     0x26 0x0dbc     0x27 0x0d4c     0x28 0x0cdc
0x29 0x0c78     0x2a 0x0c14     0x2b 0x0bb0     0x2c 0x0aa4     0x2d 0x0a38
0x2e 0x09bc     0x2f 0x0950     0x30 0x08d4     0x31 0x0890     0x32 0x0834
0x33 0x07b4     0x34 0x0758     0x35 0x06d8     0x36 0x064c



--xid 事务id
--cnt 回滚段的数量,0x36=54
--irb 表示回滚段的开始REC (record)
*-----------------------------
* Rec #0x36  slt: 0x0a  objn: 73300(0x00011e54)  objd: 73300  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: 0x0100044d.00ca.28 ctl max scn: 0x00000000002253c6 prv tx scn: 0x00000000002253e4
txn start scn: scn: 0x0000000000225c1c logon user: 106
 prev brb: 16778314 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01c0016c  hdba: 0x01c0016a   
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 1
col  1: [ 5]  53 43 4f 54 54


SQL> select dump('SCOTT',16) FROM DUAL;
DUMP('SCOTT',16)
--------------------------------------------------------
Typ=96 Len=5: 53,43,4f,54,54

   t1表有两列，回滚段里只存了一列
   
   
   
   0x01c0016c