SQL> create table t1(id int , name varchar2(10)) tablespace users pctfree 99;
表已创建。
SQL> insert into t1 values(1 , 'a');
已创建 1 行。
SQL> insert into t1 values(2 , 'b');
已创建 1 行。
SQL> insert into t1 values(3 , 'c');
已创建 1 行。
SQL> insert into t1 values(4 , 'd');
已创建 1 行。
SQL> insert into t1 values(5 , 'e');
已创建 1 行。
SQL> insert into t1 values(6 , 'f');
已创建 1 行。
SQL> insert into t1 values(7 , 'g');
已创建 1 行。
SQL> insert into t1 values(8 , 'h');
已创建 1 行。
SQL> insert into t1 values(9 , 'i');
已创建 1 行。
SQL> select rowid, dbms_rowid.rowid_object(rowid) dobj,dbms_rowid.rowid_relative
_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) block_number ,dbms_rowid.ro
wid_row_number(rowid) row_number, id , name from t1;
ROWID DOBJ FNO BLOCK_NUMBER ROW_NUMBER ID N
------------------ ---------- ---------- ------------ ---------- ---- -
AAAC5RAAEAAAABMAAA 11857 4 76 0 1 a
AAAC5RAAEAAAABMAAB 11857 4 76 1 2 b
AAAC5RAAEAAAABMAAC 11857 4 76 2 3 c
AAAC5RAAEAAAABMAAD 11857 4 76 3 4 d
AAAC5RAAEAAAABMAAE 11857 4 76 4 5 e
AAAC5RAAEAAAABMAAF 11857 4 76 5 6 f
AAAC5RAAEAAAABMAAG 11857 4 76 6 7 g
AAAC5RAAEAAAABNAAA 11857 4 77 0 8 h
AAAC5RAAEAAAABNAAB 11857 4 77 1 9 i
已选择9行。
SQL>
--===========================
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,start_ubablk from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK START_UBABLK
---------- ---------- ---------- ---------- ---------- ------------
23 21 350 10 1617 1617
SQL>
--===========================================
trace信息:
--===========================================
* Rec #0xf slt: 0x15 objn: 11857(0x00002e51) objd: 11857 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: 0x00000000
*-----------------------------
uba: 0x02800918.0091.3d ctl max scn: 0x0000.000a0453 prv tx scn: 0x0000.000a046c
txn start scn: scn: 0x0000.000a5e7b logon user: 0
prev brb: 41945366 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
*-----------------------------
* Rec #0x10 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x0f
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: 0x02800651.0092.0f
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1)
*-----------------------------
* Rec #0x11 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x10
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: 0x02800651.0092.10
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2)
*-----------------------------
* Rec #0x12 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x11
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: 0x02800651.0092.11
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 3(0x3)
*-----------------------------
* Rec #0x13 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x12
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: 0x02800651.0092.12
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 4(0x4)
*-----------------------------
* Rec #0x14 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x13
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: 0x02800651.0092.13
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 5(0x5)
*-----------------------------
* Rec #0x15 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x14
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: 0x02800651.0092.14
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004c hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 6(0x6)
*-----------------------------
* Rec #0x16 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x15
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004d hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
*-----------------------------
* Rec #0x17 slt: 0x15 objn: 11857(0x00002e51) objd: 11857 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x16
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: 0x02800651.0092.16
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100004d hdba: 0x0100004b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1)
End dump data blocks tsn: 9 file#: 10 minblk 1617 maxblk 1617
--=============================
undo block被dump之后我们清楚的发现:
insert into t1表的每一条数据在undo中都对应着一个Rec,具体rec中记录的最有用的信息是:
1.objn: 11857(0x00002e51) --data_object_id
2.bdba: 0x0100004d --relative_fno block_number
3.slot: 1(0x1) --row_number
以最后插入的insert into t1 values(9 , 'i')为例:
先把2中的bdba分解一下:
SQL> select dbms_utility.data_block_address_file(to_number('0100004d','xxxxxxxx'
)) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(TO_NUMBER('0100004D','XXXXXXXX'))
----------------------------------------------------------------------
4
SQL> select dbms_utility.data_block_address_block(to_number('0100004d','xxxxxxxx
')) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(TO_NUMBER('0100004D','XXXXXXXX'))
-----------------------------------------------------------------------
77
SQL>
objn和上面分解出来的relative_fno、block_number以及row_number(slot)
合起来不正是其对应的rowid嘛
如果看起来还不直观的话再成生rowid看看:
SQL> select dbms_rowid.rowid_create(1,11857,4,77,1) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAC5RAAEAAAABNAAB
SQL>
这就是很多书上以及很多人们常说的insert操作会把rowid放在undo的原因吧,其实
不是直接放的rowid,也是拼凑出来的,这样也符合rowid的含义:伪列
如果rollback的话,那么根据保存在undo中的rowid直接删除插入的行。
insert使用undo不太容易理解,因为rowid需要拼凑,而update和delete非常容易理解,可以直接在undo中很容易的找到其old image,这里就不赘述了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1010520/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/19602/viewspace-1010520/