oracle在没有提交的时候就可能将数据写入到文件中,此时undo怎样记录的?
SQL> create table test(id int,name varchar2(10));
Table created.
SQL> insert into test values(1,'a');
1 row created.
SQL> insert into test values(2,'b');
1 row created.
SQL> insert into test values(3,'c');
1 row created.
SQL> insert into test values(4,'d');
1 row created.
SQL> insert into test values(5,'e');
1 row created.
SQL> insert into test values(6,'f');
1 row created.
SQL> insert into test values(7,'g');
1 row created.
SQL> insert into test values(8,'h');
1 row created.
SQL> insert into test values(9,'i');
1 row created.
并没有进行提交
SQL> col fno for 9,999,999
SQL> col bno for 9,999,999
SQL> col rno for 9,999,999
SQL> select rowid,dbms_rowid.rowid_object(rowid) robject,dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) bno,dbms_rowid.rowid_row_number(rowid) rno,id,name from test;
ROWID ROBJECT FNO BNO RNO ID NAME
------------------ ---------- ---------- ---------- ---------- ---------- ----------------------------------------
AAAPAVAAEAAAAisAAA 61461 4 2,220 0 1 a
AAAPAVAAEAAAAisAAB 61461 4 2,220 1 2 b
AAAPAVAAEAAAAisAAC 61461 4 2,220 2 3 c
AAAPAVAAEAAAAisAAD 61461 4 2,220 3 4 d
AAAPAVAAEAAAAisAAE 61461 4 2,220 4 5 e
AAAPAVAAEAAAAisAAF 61461 4 2,220 5 6 f
AAAPAVAAEAAAAisAAG 61461 4 2,220 6 7 g
ROWID ROBJECT FNO BNO RNO ID NAME
------------------ ---------- ---------- ---------- ---------- ---------- ----------------------------------------
AAAPAVAAEAAAAisAAH 61461 4 2,220 7 8 h
AAAPAVAAEAAAAisAAI 61461 4 2,220 8 9 i
9 rows selected.
事务没有提交可以通过v$transaction视图查看当前的事务
SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,start_ubablk from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK START_UBABLK
---------- ---------- ---------- ---------- ---------- ------------
2 32 2069 2 1350 1350
并将undo中记录的文件号dump出来
SQL> alter session set tracefile_identifier='undo_insrt_test';
Session altered.
SQL> alter system dump datafile 2 block 1350;
System altered.
dump出来的文件如下
*-----------------------------
* Rec #0x15 slt: 0x20 objn: 61461(0x0000f015) objd: 61461 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: 0x00800546.0494.11 ctl max scn: 0x0000.004c32f4 prv tx scn: 0x0000.004c33ce
txn start scn: scn: 0x0000.004c4536 logon user: 54
prev brb: 8389937 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: 0x010008ac hdba: 0x010008ab
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
*-----------------------------
* Rec #0x16 slt: 0x20 objn: 61461(0x0000f015) objd: 61461 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: 0x02 ver: 0x01
op: C uba: 0x00800546.0494.15
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010008ac hdba: 0x010008ab
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1)
*-----------------------------
* Rec #0x17 slt: 0x20 objn: 61461(0x0000f015) objd: 61461 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: 0x00800546.0494.16
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010008ac hdba: 0x010008ab
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2)
*-----------------------------
* Rec #0x18 slt: 0x20 objn: 61461(0x0000f015) objd: 61461 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x17
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: 0x00800546.0494.17
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010008ac hdba: 0x010008ab
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 3(0x3)
*-----------------------------
* Rec #0x19 slt: 0x20 objn: 61461(0x0000f015) objd: 61461 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x18
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: 0x00800546.0494.18
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010008ac hdba: 0x010008ab
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 4(0x4)
*-----------------------------
* Rec #0x1a slt: 0x20 objn: 61461(0x0000f015) objd: 61461 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x19
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: 0x00800546.0494.19
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010008ac hdba: 0x010008ab
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 5(0x5)
*-----------------------------
* Rec #0x1b slt: 0x20 objn: 61461(0x0000f015) objd: 61461 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x1a
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: 0x00800546.0494.1a
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010008ac hdba: 0x010008ab
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 6(0x6)
*-----------------------------
* Rec #0x1c slt: 0x20 objn: 61461(0x0000f015) objd: 61461 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x1b
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: 0x00800546.0494.1b
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010008ac hdba: 0x010008ab
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 7(0x7)
*-----------------------------
* Rec #0x1d slt: 0x20 objn: 61461(0x0000f015) objd: 61461 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x1c
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: 0x00800546.0494.1c
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010008ac hdba: 0x010008ab
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 8(0x8)
根据dump出来的文件可以明显的看到
1.objn: 61461(0x0000f015)--data_object_id
2.bdba: 0x010008ac --relative_fno block_number
3.slot: 0(0x0)--row_number
SQL> select dbms_utility.data_block_address_file(to_number('010008ac','xxxxxxxx')) fno,dbms_utility.data_block_address_block(to_number('010008ac','xxxxxxxx')) bno from dual;
FNO BNO
---------- ----------
4 2220
可以看出dump出的文件正是相应的数据行
SQL> select dbms_rowid.rowid_create(1,61461,4,2220,0) from dual; --第一位1表示占位的1
DBMS_ROWID.ROWID_C
------------------
AAAPAVAAEAAAAisAAA
与前面的rowid对应,所以oracle通过计算后将rowid存放在dump文件中,oracle通过计算出rowid找到对应的数据。即使oracle rollback也不会删掉数据文件中的对应的数据。