[20130312]undo与DML-insert.txt
闲着没事,研究一下undo与DML.
DML操作在UNDO中的信息
通过Dump UNDO Block观察到DML操作记录在UNDO中的信息,主要为以下内容:
1、对于Insert操作,需要在UNDO中记录插入行的ROWID.
2、对于Update操作,需要在UNDO中记录被更新列的前镜像的值,同时也会记录被更新行的ROWID。
3、对于Delete操作,需要在UNDO中记录被删除行所有列的值(前镜像)及ROWID。
1、先看来看Insert操作
--先建立一个测试表,选择都是字符类型是为了显示方便.而且不建立所以,这样好分析一些.
SQL> select * from v$version where rownumBANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
create table t (a varchar2(10),b varchar2(10));
insert into t values('1','a');
insert into t values('2','b');
insert into t values('3','c');
commit;
SQL> select t.rowid,t.* from t;
ROWID A B
------------------ ---------- ----------
AABB8BAAEAAAAWfAAA 1 a
AABB8BAAEAAAAWfAAB 2 b
AABB8BAAEAAAAWfAAC 3 c
SQL> @lookup_rowid AABB8BAAEAAAAWfAAA
OBJECT FILE BLOCK ROW
---------- ---------- ---------- ----------
270081 4 1439 0
--插入两条记录,不提交!
SQL> insert into t values('4','d');
1 row created.
SQL> select dbms_transaction.local_transaction_id() x from dual ;
X
------------------------------
6.9.11217
SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,STATUS from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC STATUS
---------- ---------- ---------- ---------- ---------- ---------- ----------------
6 9 11217 3 13482 51 ACTIVE
SQL> insert into t values('5','e');
1 row created.
SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,STATUS from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC STATUS
---------- ---------- ---------- ---------- ---------- ---------- ----------------
6 9 11217 3 13482 52 ACTIVE
SQL> select dbms_transaction.local_transaction_id() x from dual ;
X
------------------------------
6.9.11217
SQL> select t.rowid,t.* from t;
ROWID A B
------------------ ---------- ----------
AABB8BAAEAAAAWfAAA 1 a
AABB8BAAEAAAAWfAAB 2 b
AABB8BAAEAAAAWfAAC 3 c
AABB8BAAEAAAAWfAAD 4 d
AABB8BAAEAAAAWfAAE 5 e
--从以上操作发现,在插入一条后UBAREC从51(0x33)=>52(0x34).
--从以上信息,可以知道在datafile 3 block 13482.
--转储看看UBAREC=52的情况:(0x34)
alter system checkpoint ;
alter system dump datafile 3 block 13482;
********************************************************************************
UNDO BLK:
xid: 0x0006.009.00002bd1 seq: 0x14c3 cnt: 0x34 irb: 0x34 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f70 0x02 0x1f18 0x03 0x1ec4 0x04 0x1e14 0x05 0x1d98
0x06 0x1d10 0x07 0x1cb4 0x08 0x1c04 0x09 0x1b88 0x0a 0x1b1c
0x0b 0x1ab0 0x0c 0x1a44 0x0d 0x1994 0x0e 0x1928 0x0f 0x18b8
0x10 0x1830 0x11 0x17d0 0x12 0x1748 0x13 0x16e4 0x14 0x165c
0x15 0x15f8 0x16 0x15b4 0x17 0x1560 0x18 0x151c 0x19 0x14c8
0x1a 0x1484 0x1b 0x1430 0x1c 0x13ec 0x1d 0x1398 0x1e 0x1354
0x1f 0x1300 0x20 0x1278 0x21 0x1214 0x22 0x11d0 0x23 0x117c
0x24 0x1138 0x25 0x10e4 0x26 0x10a0 0x27 0x104c 0x28 0x0f9c
0x29 0x0f48 0x2a 0x0ee4 0x2b 0x0ea0 0x2c 0x0e4c 0x2d 0x0dd0
0x2e 0x0d60 0x2f 0x0cd8 0x30 0x0c74 0x31 0x0bc8 0x32 0x0b40
0x33 0x0ad0 0x34 0x0a8c
*-----------------------------
--其中"Rec: 0x34"代表UNDO BLOCK中事务恢复的结尾. 地址是0x0a8c.
*-----------------------------
* Rec #0x33 slt: 0x09 objn: 270081(0x00041f01) objd: 270081 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: 0x00c034aa.14c3.32 ctl max scn: 0x0000.c0191f78 prv tx scn: 0x0000.c0191f82
txn start scn: scn: 0x0000.c0192b08 logon user: 84
prev brb: 12596432 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: 0x0100059f hdba: 0x0100059a
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 3(0x3)
*-----------------------------
* Rec #0x34 slt: 0x09 objn: 270081(0x00041f01) objd: 270081 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x33
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
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c034aa.14c3.33
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0100059f hdba: 0x0100059a
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 4(0x4)
--看看Rec=0x34,objn: 270081(0x00041f01) objd: 270081就是对应的对象.tblspc: 4(0x00000004)对应的就是文件编号.
--bdba: 0x0100059f ,里面就包含了块号. slot: 4(0x4)对应的就是行号4.
--hdba: 0x0100059a,表示该对象的块头.
--另外由于该事务包含两个insert操作.rci=0x33,表示前面的事务.
--如果看Rec #0x33可以发现rci=0x00,表示事务开始.
SQL> @dfb 0100059f
RFILE# BLOCK#
---------- ----------
4 1439
SQL> @dfb 0100059a
RFILE# BLOCK#
---------- ----------
4 1434
SQL> select object_name, OBJECT_ID ,DATA_OBJECT_ID from dba_objects where object_name='T' and wner=user;
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- --------- --------------
T 270081 270081
SQL> select header_file,header_block from dba_segments where wner=user and segment_name='T';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 1434
--虽然在insert时转储的undo并没有显示的标明rowid信息,但是可以从许多信息拼凑出来的.
时间: 2024-09-20 12:41:21