[20130312]undo与DML-insert.txt

[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信息,但是可以从许多信息拼凑出来的.