DML操作记录在UNDO中的信息（undo记录里包含了其对应的数据行的ROWID（并非直接记录，而是拼凑出来的））

DML操作记录在UNDO中的信息

                    作者 ： dbaroad【转载时请务必以超链接形式标明文章原始出处和作者信息】
                    链接： http://www.dbaroad.me/archives/2009/03/dml_in_undo_rec.html

DML操作在UNDO中的信息

通过Dump UNDO Block观察到DML操作记录在UNDO中的信息，主要为以下内容：
1、对于Insert操作，需要在UNDO中记录插入行的ROWID（并非直接记录，而是拼凑出来的）。
2、对于Update操作，需要在UNDO中记录被更新列的前镜像，同时也会记录被更新行的ROWID。
3、对于Delete操作，需要在UNDO中记录被删除行所有列的值（前镜像）及ROWID。

1、先看来看Insert操作

SQL> create table test (a number,b varchar2(10));   Table created.   SQL> insert into test values(1,'a');   1 row created.   SQL> insert into test values(2,'a');   1 row created.   SQL> insert into test values(2,'b');   1 row created.   SQL> select dbms_rowid.ROWID_OBJECT(rowid) data_object_id#, 2 dbms_rowid.ROWID_RELATIVE_FNO(rowid) rfile#, 3 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#, 4 dbms_rowid.ROWID_ROW_NUMBER(rowid) row# 5 from test;   DATA_OBJECT_ID# RFILE# BLOCK# ROW# --------------- ---------- ---------- ---------- 6469 1 15326 0 6469 1 15326 1 6469 1 15326 2   SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,STATUS 2 from v$transaction;   XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC STATUS ---------- ---------- ---------- ---------- ---------- ---------- --------- 10 24 59 8 46 8 ACTIVE   SQL> alter system dump datafile 8 block 46;   System altered.   SQL> oradebug setmypid Statement processed. SQL> oradebug tracefile_name /app/oracle/admin/DBA/udump/dba_ora_28469.trc SQL>

查看trace文件，找到相关UNDO Rec的信息：

UNDO BLK: xid: 0x000a.018.0000003b seq: 0x8ab cnt: 0x8 irb: 0x8 icl: 0x0 flg: 0x0000   Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x1f64 0x02 0x1edc 0x03 0x1e7c 0x04 0x1e0c 0x05 0x1da4 0x06 0x1d4c 0x07 0x1d04 0x08 0x1cbc

其中“irb: 0×8”代表UNDO BLOCK中事务恢复的起点

*----------------------------- * Rec #0x6 slt: 0x18 objn: 6469(0x00001945) objd: 6469 tblspc: 0(0x00000000) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- uba: 0x0200002e.08ab.04 ctl max scn: 0x09c4.310dd0bb prv tx scn: 0x09c4.310dd183 KDO undo record: KTB Redo op: 0x03 ver: 0x01 op: Z KDO Op code: DRP row dependencies Disabled xtype: XA bdba: 0x00403bde hdba: 0x00403bdd itli: 1 ispac: 0 maxfr: 4863 tabn: 0 slot: 0(0x0)   *----------------------------- * Rec #0x7 slt: 0x18 objn: 6469(0x00001945) objd: 6469 tblspc: 0(0x00000000) * Layer: 11 (Row) opc: 1 rci 0x06 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- KDO undo record: KTB Redo op: 0x02 ver: 0x01 op: C uba: 0x0200002e.08ab.06 KDO Op code: DRP row dependencies Disabled xtype: XA bdba: 0x00403bde hdba: 0x00403bdd itli: 1 ispac: 0 maxfr: 4863 tabn: 0 slot: 1(0x1)   *----------------------------- * Rec #0x8 slt: 0x18 objn: 6469(0x00001945) objd: 6469 tblspc: 0(0x00000000) * Layer: 11 (Row) opc: 1 rci 0x07 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- KDO undo record: KTB Redo op: 0x02 ver: 0x01 op: C uba: 0x0200002e.08ab.07 KDO Op code: DRP row dependencies Disabled xtype: XA bdba: 0x00403bde hdba: 0x00403bdd itli: 1 ispac: 0 maxfr: 4863 tabn: 0 slot: 2(0x2)

没发现ROWID的影子，看来是要通过一些字段拼凑起来了：
“objd: 6469”对应ROWID中的DATA_OBJECT_ID#
“bdba: 0x00403bde”对应ROWID中的rfile#，block#
“slot: 2(0×2)”对应ROWID中的ROW#
bdba的拆分如下：

SQL> select 2 dbms_utility.data_block_address_file(to_number('00403bde','xxxxxxxx')) rfile# 3 from dual;   RFILE# ---------- 1   SQL> select 2 dbms_utility.data_block_address_block(to_number('00403bde','xxxxxxxx')) block# 3 from dual;   BLOCK# ---------- 15326   SQL>

2、Update操作

SQL> select * from test;   A B ---------- ---------- 1 a 2 a 2 b   SQL> update test set a=3 where a=1;   1 row updated.   SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,STATUS 2 from v$transaction;   XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC STATUS ---------- ---------- ---------- ---------- ---------- ---------- --------- 10 33 59 8 46 17 ACTIVE   SQL> alter system dump datafile 8 block 46;   System altered.   SQL> oradebug setmypid Statement processed. SQL> oradebug tracefile_name /app/oracle/admin/DBA/udump/dba_ora_1577.trc

截取相关UNDO BLOCK DUMP信息：

UNDO BLK: xid: 0x000a.021.0000003b seq: 0x8ab cnt: 0x11 irb: 0x11 icl: 0x0 flg: 0x0000   Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x1f64 0x02 0x1edc 0x03 0x1e7c 0x04 0x1e0c 0x05 0x1da4 0x06 0x1d4c 0x07 0x1d04 0x08 0x1cbc 0x09 0x1c0c 0x0a 0x1b5c 0x0b 0x1aac 0x0c 0x19fc 0x0d 0x194c 0x0e 0x189c 0x0f 0x17ec 0x10 0x173c 0x11 0x16c4

*----------------------------- * Rec #0x11 slt: 0x21 objn: 6469(0x00001945) objd: 6469 tblspc: 0(0x00000000) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- uba: 0x0200002e.08ab.10 ctl max scn: 0x09c4.310dd637 prv tx scn: 0x09c4.310dd7c9 KDO undo record: KTB Redo op: 0x03 ver: 0x01 op: Z KDO Op code: URP row dependencies Disabled xtype: XA bdba: 0x00403bde hdba: 0x00403bdd itli: 2 ispac: 0 maxfr: 4863 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 1 ncol: 2 nnew: 1 size: 0 col 0: [ 2] c1 02

这里记录的“col 0: [ 2] c1 02”也就是update的前镜像，也就是更新前的值

SQL> select dump(1,16) from dual;   DUMP(1,16) ----------------- Typ=2 Len=2: c1,2

与Insert操作相同，这样Undo Rec中也包含了ROWID的信息。

3、Delete操作

SQL> select * from test;   A B ---------- ---------- 1 a 2 a 2 b   SQL> delete from test where a=1;   1 row deleted.   SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,STATUS 2 from v$transaction;   XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC STATUS ---------- ---------- ---------- ---------- ---------- ---------- --------- 10 35 59 8 45 39 ACTIVE   SQL> alter system dump datafile 8 block 45;   System altered.   SQL> oradebug setmypid; Statement processed. SQL> oradebug tracefile_name /app/oracle/admin/DBA/udump/dba_ora_10350.trc

截取相关UNDO BLOCK DUMP信息：

UNDO BLK: xid: 0x000a.023.0000003b seq: 0x8ab cnt: 0x27 irb: 0x27 icl: 0x0 flg: 0x0000   Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x1f74 0x02 0x1f2c 0x03 0x1e94 0x04 0x1e24 0x05 0x1dbc 0x06 0x1d64 0x07 0x1cf4 0x08 0x1cbc 0x09 0x1b9c 0x0a 0x1b2c 0x0b 0x1abc 0x0c 0x1a4c 0x0d 0x193c 0x0e 0x18dc 0x0f 0x187c 0x10 0x181c 0x11 0x16cc 0x12 0x15bc 0x13 0x14b4 0x14 0x1444 0x15 0x13cc 0x16 0x129c 0x17 0x1244 0x18 0x11b4 0x19 0x1144 0x1a 0x10dc 0x1b 0x106c 0x1c 0x1014 0x1d 0x0fcc 0x1e 0x0f64 0x1f 0x0ef4 0x20 0x0e8c 0x21 0x0e44 0x22 0x0dec 0x23 0x0d8c 0x24 0x0d34 0x25 0x0c24 0x26 0x0bb4 0x27 0x0b24

*----------------------------- * Rec #0x27 slt: 0x23 objn: 6469(0x00001945) objd: 6469 tblspc: 0(0x00000000) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- uba: 0x0200002d.08ab.26 ctl max scn: 0x09c4.310dd891 prv tx scn: 0x09c4.310dd8f6 KDO undo record: KTB Redo op: 0x03 ver: 0x01 op: Z KDO Op code: IRP row dependencies Disabled xtype: XA bdba: 0x00403bde hdba: 0x00403bdd itli: 2 ispac: 0 maxfr: 4863 tabn: 0 slot: 0(0x0) size/delt: 8 fb: --H-FL-- lb: 0x0 cc: 2 null: -- col 0: [ 2] c1 02 col 1: [ 1] 61

这里“col 0: [ 2] c1 02”与“col 1: [ 1] 61”也就是被删除行的前镜像了，通过DUMP来对比下：

SQL> select dump(1,16) from dual;   DUMP(1,16) ----------------- Typ=2 Len=2: c1,2   SQL> select dump('a',16) from dual;   DUMP('A',16) ---------------- Typ=96 Len=1: 61

同样的，这些UNDO Rec也包含了被删除行的ROWID信息

— The End —