[20130312]undo与DML-update.txt

[20130312]undo与DML-update.txt

闲着没事,研究一下undo与DML.

DML操作在UNDO中的信息

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

1、接着前面的测试继续测试update操作:
--先建立一个测试表,选择都是字符类型是为了显示方便.而且不建立使用,这样好分析一些.

SQL> select * from v$version where rownum

BANNER

--------------------------------------------------------------------------------

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

2.执行修改不提交:

SQL> update t set b='ddd' where a in ('1','3');

2 rows updated.

SQL> select dbms_transaction.local_transaction_id()  x from dual ;

X

------------------------------

5.12.13794

SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC,STATUS from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBAREC STATUS

---------- ---------- ---------- ---------- ---------- ---------- ----------------

         5         12      13794          3      20327         15 ACTIVE

--从以上操作发现,在修改二条记录后UBAREC=15(0x0f).
--从以上信息，可以知道在datafile 3 block 20327.
--转储看看UBAREC=15的情况:(0x0f)

alter system checkpoint ;

alter system dump datafile 3 block 20327;

********************************************************************************

UNDO BLK:

xid: 0x0005.00c.000035e2  seq: 0x1709 cnt: 0xf   irb: 0xf   icl: 0x0   flg: 0x0000

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset

---------------------------------------------------------------------------

0x01 0x1f90     0x02 0x1f38     0x03 0x1e8c     0x04 0x1de0     0x05 0x1d34

0x06 0x1c84     0x07 0x1bd4     0x08 0x1b4c     0x09 0x1aec     0x0a 0x1a8c

0x0b 0x1580     0x0c 0x1518     0x0d 0x14b0     0x0e 0x1428     0x0f 0x13cc

*-----------------------------

* Rec #0xe  slt: 0x0c  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: 0x00c04f67.1709.08 ctl max scn: 0x0000.c01937bc prv tx scn: 0x0000.c01937bf

txn start scn: scn: 0x0000.c0193b6c logon user: 84

 prev brb: 12603227 prev bcl: 0

KDO undo record:

KTB Redo

op: 0x03  ver: 0x01

compat bit: 4 (post-11) padding: 1

op: Z

KDO Op code: URP row dependencies Disabled

  xtype: XA flags: 0x00000000  bdba: 0x0100059f  hdba: 0x0100059a

itli: 2  ispac: 0  maxfr: 4858

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0

ncol: 2 nnew: 1 size: -2

col  1: [ 1]  61

*-----------------------------

* Rec #0xf  slt: 0x0c  objn: 270081(0x00041f01)  objd: 270081  tblspc: 4(0x00000004)

*       Layer:  11 (Row)   opc: 1   rci 0x0e

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: 0x00c04f67.1709.0e

KDO Op code: URP row dependencies Disabled

  xtype: XA flags: 0x00000000  bdba: 0x0100059f  hdba: 0x0100059a

itli: 2  ispac: 0  maxfr: 4858

tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0

ncol: 2 nnew: 1 size: -2

col  1: [ 1]  63

--看看Rec=0x0f,objn: 270081(0x00041f01)  objd: 270081就是对应的对象.tblspc: 4(0x00000004)对应的就是文件编号.
--bdba: 0x0100059f ,里面就包含了块号. slot: 4(0x2)对应的就是行号2.
--hdba: 0x0100059a,表示该对象的块头.
--另外由于该事务修改了两行记录.rci=0x0e,表示前面的事务.
--如果看Rec #0x0e可以发现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

--col  1: [ 1]  63对应的就是'c',col  1: [ 1]  61对应的就是'a'.也就是修改前的前映像值.