Oracle事务原理探究2--读书笔记五

续上篇...

3.  数据块访问与undo

        任何时候当会话查看一个数据块的时候，都需要保证看到的是适当的数据版本。从外部观点来看，这意味着会话不应该看到任何未提交的数据，或许还不应该看到查询开始后修改并提交的数据（取决于事务的隔离级别），这称为数据的读一致性版本。

        下面我们来谈谈oracle怎么实现读一致性版本的：

3.1 设置场景

        我们会创建一张表，里面插入3条数据，然后开几个会话对其进行操作

3.1.1 连接oracle，以任何一个用户登录即可，初始化一些格式

execute dbms_random.seed(0)set doc offdocend doc is marked with ##set linesize 120set trimspool onset pagesize 24set arraysize 25-- set longchunksize 32768-- set long 32768set autotrace offclear breaksttitle offbtitle offcolumn owner format a15column segment_name format a20column table_name format a20column index_name format a20column object_name format a20column subobject_name format a20column partition_name format a20column subpartition_name format a20column column_name format a20column column_expression format a40 word wrapcolumn constraint_name format a20column referenced_name format a30column file_name format a60column low_value format a24column high_value format a24column parent_id_plus_expformat 999column id_plus_expformat 990column plan_plus_exp format a90column object_node_plus_expformat a14column other_plus_expformat a90column other_tag_plus_expformat a29column access_predicatesformat a80column filter_predicatesformat a80column projectionformat a80column remarksformat a80column partition_startformat a12column partition_stopformat a12column partition_idformat 999column other_tagformat a32column object_aliasformat a24column object_nodeformat a13columnotherformat a150column os_usernameformat a30column terminalformat a24column userhostformat a24column client_idformat a24column statistic_name format a35column namespace format a20column attribute format a20column hint format a40column start_timeformat a25column end_timeformat a25column time_now noprint new_value m_timestampset feedback offselect to_char(sysdate,'hh24miss') time_now from dual;commit;set feedback onset timing offset verify offalter session set optimizer_mode = all_rows;spool log

3.1.2 创建表

drop table t1;create table t1(id number, n1 number);insert into t1 values(1,1);insert into t1 values(2,2);insert into t1 values(3,3);commit;create unique index t1_i1 on t1(id);begindbms_stats.gather_table_stats(ownname => user,tabname =>'T1',estimate_percent => 100,method_opt => 'for all columns size 1');end;/----<span style="white-space:pre"></span>For 11g - force to disc for the dump--alter system checkpoint;

3.1.3 创建一个存储过程，用来转储一个表使用的第一个数据块。

create or replace procedure dump_table_block(i_tab_nameinvarchar2,i_ownerinvarchar2default sys_context('userenv','session_user'))asm_file_idnumber;m_blocknumber;m_processvarchar2(32);beginexecute immediate' select ' ||' dbms_rowid.rowid_relative_fno(rowid), ' ||' dbms_rowid.rowid_block_number(rowid)  ' ||' from ' ||i_owner || '.' ||i_tab_name ||' where ' ||' rownum = 1 'intom_file_id, m_block;execute immediate'alter system dump datafile ' || m_file_id ||' block ' || m_block;----For non-MTS, work out the trace file name--selectspidintom_processfromv$sessionse,v$processprwhere----The first option is the 9.2 version for checking the SID--The second is a quick and dirty option for 8.1.7--provided SYS has made v$mystat visible (or this is the sys account)----se.sid = (select dbms_support.mysid from dual)se.sid = (select sid from v$mystat where rownum = 1)andpr.addr = se.paddr;dbms_output.new_line;dbms_output.put_line('Trace file name includes: ' || m_process);dbms_output.new_line;exceptionwhen others thendbms_output.new_line;dbms_output.put_line('Unspecified error.');dbms_output.put_line('Check syntax.');dbms_output.put_line('dump_table_block({table_name},[{owner}]');dbms_output.new_line;raise;end;./show errorsdrop public synonym dump_table_block;create public synonym dump_table_block for dump_table_block;grant execute on dump_table_block to public;

3.1.4 转储表t1的第一个数据块

execute dump_table_block('t1')

3.1.5 我们会看到：Trace file name includes: 4292这样的字样，到oracle 的trace目录找到这个跟踪文件，我的电脑入戏所示：

E:\app\Administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4292.trc,  双击用记事本打开，可以看到如下内容：

Block header dump:  0x00416169 Object id on Block? Y seg/obj: 0x12e7a  csc: 0x00.326fb7  itc: 2  flg: O  typ: 1 - DATA     fsl: 0  fnx: 0x0 ver: 0x01  Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0002.005.000005f5  0x00c00b18.0121.0d  --U-    3  fsc 0x0000.00326fb80x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x00416169data_block_dump,data header at 0x1e256e5c===============tsiz: 0x1fa0hsiz: 0x18pbl: 0x1e256e5c     76543210flag=--------ntab=1nrow=3frre=-1fsbo=0x18fseo=0x1f85avsp=0x1f6dtosp=0x1f6d0xe:pti[0]nrow=3offs=00x12:pri[0]offs=0x1f970x14:pri[1]offs=0x1f8e0x16:pri[2]offs=0x1f85block_row_dump:tab 0, row 0, @0x1f97tl: 9 fb: --H-FL-- lb: 0x1  cc: 2col  0: [ 2]  c1 02col  1: [ 2]  c1 02tab 0, row 1, @0x1f8etl: 9 fb: --H-FL-- lb: 0x1  cc: 2col  0: [ 2]  c1 03col  1: [ 2]  c1 03tab 0, row 2, @0x1f85tl: 9 fb: --H-FL-- lb: 0x1  cc: 2col  0: [ 2]  c1 04col  1: [ 2]  c1 04end_of_block_dumpEnd dump data blocks tsn: 0 file#: 1 minblk 90473 maxblk 90473Start dump data blocks tsn: 0 file#:1 minblk 90473 maxblk 90473

3.2 事务列表
        这一节，简要介绍一下转储出来的数据块中事务槽的信息，上面数据块的事务槽如下：

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0002.005.000005f5  0x00c00b18.0121.0d  --U-    3  fsc 0x0000.00326fb80x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Itl：列表的数组索引，该值未真正存储在数据块中，它由执行转储的代码生成。该值用在行的锁字节(lb:)中以显示哪个事务锁住了该行。

Xid: 最近更改该块的事务的事务id，格式是undo段.undo槽.undo序列号.

Uba: undo记录地址，是事务为该块最近生成的undo记录所在块的序列号。

Flag: 标识事务当前状态

          ----  活动（当Xid中每一个字段为0时表示，无事务）

          --U- 上界提交（表明这个事务已经提交，只是还没有清除一些标记）

         C---： 已提交并清除（所有标记已清除，比如相关的锁字节都被置0了）

 Lck：块中由该事务锁住的行数

Scn/Fsc：表示提交SCN或者快速提交SCN。

       在我们这个例子中，占用了一个事务槽，flag是--U-表明，事务已经快速提交，但是Lck为3，表明还没有清除锁标记，快速提交的scn是326fb8. Uba指向了最后一条插入的undo记录，这条undo记录会指向上一条插入的undo记录，上一条undo记录指向了上上条插入的undo记录。这样，如果事务失败，或者人工回滚，沿着这条undo链重做就好了。在oracle10g之后，一个数据块的事务槽被硬性规定为169个。（8KB大小的情况下）

3.3 并发操作

我们需要开启4个事务，如下所示：

session1: update t1 set n1=101  where id = 1;session2: update t1 set n1=102 where id = 2;                  commit;alter system checkpoint;My session: set transaction read only;session3: update t1 set n1=99 where id = 3; commit; alter system checkpoint;My session: select id, n1 from t1;

我们在自己的会话查询之前，转储一下数据块的结果，不过转储之前执行一下切换检查点命令（alter system checkpoint;），使改变刷新输出到磁盘。

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0009.010.0000062d  0x00c00712.0127.07  --U-    1  fsc 0x0000.0034a241 --flag U 代表上界提交，意思是这个事务已经提交，只是目前标记还没清理。0x02   0x0005.018.0000062a  0x00c0068a.013f.2f  ----    1  fsc 0x0000.00000000 ----锁住的是id为1的行，为会话1所锁住的行bdba: 0x00416169data_block_dump,data header at 0x1cd0825c===============tsiz: 0x1fa0hsiz: 0x18pbl: 0x1cd0825c     76543210flag=--------ntab=1nrow=3frre=-1fsbo=0x18fseo=0x1f71avsp=0x1f6btosp=0x1f6bblock_row_dump:tab 0, row 0, @0x1f7btl: 10 fb: --H-FL-- lb: 0x2  cc: 2 -- 事务0x2似乎锁住了这一行，其实是锁住了这一行col  0: [ 2]  c1 02col  1: [ 3]  c2 02 02tab 0, row 1, @0x1f71tl: 10 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 2]  c1 03col  1: [ 3]  c2 02 03tab 0, row 2, @0x1f85tl: 9 fb: --H-FL-- lb: 0x1  cc: 2 -- 事务0x1似乎锁住了这一行，其实没有锁住，只是锁标记没有清除col  0: [ 2]  c1 04col  1: [ 2]  c1 64end_of_block_dump

 执行查询之后，转储数据块结果(需要先执行alter system checkpoint;)

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0009.010.0000062d  0x00c00712.0127.07  C---    0  scn 0x0000.0034a241 -- flag C 已提交并清除完成，表明，查询会将已提交的数据清除标记。0x02   0x0005.018.0000062a  0x00c0068a.013f.2f  ----    1  fsc 0x0000.00000000 --- 锁住的是id为1的行，为会话1所锁住的行bdba: 0x00416169data_block_dump,data header at 0x1cd0825c===============tsiz: 0x1fa0hsiz: 0x18pbl: 0x1cd0825c     76543210flag=--------ntab=1nrow=3frre=-1fsbo=0x18fseo=0x1f71avsp=0x1f6btosp=0x1f6bblock_row_dump:tab 0, row 0, @0x1f7btl: 10 fb: --H-FL-- lb: 0x2  cc: 2 -- 事务0x2似乎锁住了这一行，其实是锁住了这一行。col  0: [ 2]  c1 02col  1: [ 3]  c2 02 02tab 0, row 1, @0x1f71tl: 10 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 2]  c1 03col  1: [ 3]  c2 02 03tab 0, row 2, @0x1f85tl: 9 fb: --H-FL-- lb: 0x0  cc: 2 --锁标记已经被清除了。col  0: [ 2]  c1 04col  1: [ 2]  c1 64end_of_block_dump

3.4 真实的查询操作

       我们知道，如果我们需要查询一个数据，假设我们是事务1，开始于1:00，于1:05分开始查找A表记录1号。另一个事务2于1:01开始，在1:05分之前将A表记录1号修改了3遍，比如最开始记录1号的值是1，另一个事务将其由1改成2，又改成了3，最后改成了4。另一个事务2于1:10提交的。这样我们的事务1查找的记录1号的值应该为1，但是数据块里面的值已经被修改好几遍了，而且已经变成了4。那么oracle是怎么找到原始值1的呢。下面让我们通过一个小例子来解释这种现象：

       其实，就是讲前面的步骤在执行一下，原谅我直接将上面的命令再拷贝一部分放在下面

3.4.1 准备工作

</pre><pre code_snippet_id="571760" snippet_file_name="blog_20150104_10_4627320" name="code" class="sql">execute dbms_random.seed(0)set doc offdocend doc is marked with ##set linesize 120set trimspool onset pagesize 24set arraysize 25-- set longchunksize 32768-- set long 32768set autotrace offclear breaksttitle offbtitle offcolumn owner format a15column segment_name format a20column table_name format a20column index_name format a20column object_name format a20column subobject_name format a20column partition_name format a20column subpartition_name format a20column column_name format a20column column_expression format a40 word wrapcolumn constraint_name format a20column referenced_name format a30column file_name format a60column low_value format a24column high_value format a24column parent_id_plus_expformat 999column id_plus_expformat 990column plan_plus_exp format a90column object_node_plus_expformat a14column other_plus_expformat a90column other_tag_plus_expformat a29column access_predicatesformat a80column filter_predicatesformat a80column projectionformat a80column remarksformat a80column partition_startformat a12column partition_stopformat a12column partition_idformat 999column other_tagformat a32column object_aliasformat a24column object_nodeformat a13columnotherformat a150column os_usernameformat a30column terminalformat a24column userhostformat a24column client_idformat a24column statistic_name format a35column namespace format a20column attribute format a20column hint format a40column start_timeformat a25column end_timeformat a25column time_now noprint new_value m_timestampset feedback offselect to_char(sysdate,'hh24miss') time_now from dual;commit;set feedback onset timing offset verify offalter session set optimizer_mode = all_rows;spool log-- 创建表drop table t1;create table t1(id number, n1 number);insert into t1 values(1,1);insert into t1 values(2,2);insert into t1 values(3,3);commit;create unique index t1_i1 on t1(id);begindbms_stats.gather_table_stats(ownname => user,tabname =>'T1',estimate_percent => 100,method_opt => 'for all columns size 1');end;/----<span style="white-space:pre"></span>For 11g - force to disc for the dump--alter system checkpoint;-- 创建一个存储过程，用来转储一个表使用的第一个数据块。create or replace procedure dump_table_block(i_tab_nameinvarchar2,i_ownerinvarchar2default sys_context('userenv','session_user'))asm_file_idnumber;m_blocknumber;m_processvarchar2(32);beginexecute immediate' select ' ||' dbms_rowid.rowid_relative_fno(rowid), ' ||' dbms_rowid.rowid_block_number(rowid)  ' ||' from ' ||i_owner || '.' ||i_tab_name ||' where ' ||' rownum = 1 'intom_file_id, m_block;execute immediate'alter system dump datafile ' || m_file_id ||' block ' || m_block;----For non-MTS, work out the trace file name--selectspidintom_processfromv$sessionse,v$processprwhere----The first option is the 9.2 version for checking the SID--The second is a quick and dirty option for 8.1.7--provided SYS has made v$mystat visible (or this is the sys account)----se.sid = (select dbms_support.mysid from dual)se.sid = (select sid from v$mystat where rownum = 1)andpr.addr = se.paddr;dbms_output.new_line;dbms_output.put_line('Trace file name includes: ' || m_process);dbms_output.new_line;exceptionwhen others thendbms_output.new_line;dbms_output.put_line('Unspecified error.');dbms_output.put_line('Check syntax.');dbms_output.put_line('dump_table_block({table_name},[{owner}]');dbms_output.new_line;raise;end;./show errorsdrop public synonym dump_table_block;create public synonym dump_table_block for dump_table_block;grant execute on dump_table_block to public;

创建一个转储undo块的存储过程

create or replace procedure dump_undo_blockasm_xidusnnumber;m_header_file_idnumber;m_header_block_idnumber;m_start_file_idnumber;m_start_block_idnumber;m_file_idnumber;m_block_idnumber;m_processnumber;beginselectxidusn,start_ubafil,start_ubablk,ubafil, ubablkintom_xidusn,m_start_file_id,m_start_block_id,m_file_id,m_block_idfromv$sessionses,v$transactiontrxwhereses.sid = (select mys.sid from V$mystat mys where rownum = 1)andtrx.ses_addr = ses.saddr;select file_id, block_id intom_header_file_id,m_header_block_idfrom dba_rollback_segs where segment_id = m_xidusn;dbms_output.put_line('Header  File: ' || m_header_file_id || ' Header block: '  || m_header_block_id);dbms_output.put_line('Start   File: ' || m_start_file_id  || ' Start block: '   || m_start_block_id);dbms_output.put_line('Current File: ' || m_file_id        || ' Current block: ' || m_block_id);dbms_system.ksdwrt(1,'===================');dbms_system.ksdwrt(1,'Undo Segment Header');dbms_system.ksdwrt(1,'===================');execute immediate'alter system dump datafile ' || m_header_file_id ||' block ' || m_header_block_id;dbms_system.ksdwrt(1,'================');dbms_system.ksdwrt(1,'Undo Start block');dbms_system.ksdwrt(1,'================');execute immediate'alter system dump datafile ' || m_start_file_id ||' block ' || m_start_block_id;if m_start_block_id != m_block_id thendbms_system.ksdwrt(1,'==================');dbms_system.ksdwrt(1,'Current Undo block');dbms_system.ksdwrt(1,'==================');execute immediate'alter system dump datafile ' || m_file_id ||' block ' || m_block_id;end if;selectspidintom_processfromv$sessionse,v$processprwherese.sid = (select sid from v$mystat where rownum = 1)andpr.addr = se.paddr;dbms_output.put_line('Trace file name includes: ' || m_process);end;/grant execute on dump_undo_block to public;drop   public synonym dump_undo_block; create public synonym dump_undo_block for dump_undo_block;

        

以上步骤只是创建了一个表t1，

       3.4.2 转储表t1的第一个数据块

execute dump_table_block('t1')

        3.4.3 另外开启一个会话，将id为1的记录n1的值改为101，然后改为102，然后改为103。并记录块的变化和undo块的变化。

select * from t1;--清除标记  alter system checkpoint;--刷新输出磁盘，执行完等5,6sexecute dump_table_block('t1')--转储数据块  <span style="color:#ff0000;">序号1</span>update t1 set n1=101 where id=1;--第一次更新alter system checkpoint;--刷新输出磁盘，执行完等5,6sexecute dump_table_block('t1')--转储数据块execute dump_undo_block--转储undo块  <span style="color:#ff0000;">序号2</span>update t1 set n1=102 where id=1;--第二次更新alter system checkpoint;--刷新输出磁盘，执行完等5,6sexecute dump_table_block('t1')--转储数据块execute dump_undo_block--转储undo块  <span style="color:#ff0000;">序号3</span>update t1 set n1=103 where id=1;--第三次更新alter system checkpoint;--刷新输出磁盘，执行完等5,6sexecute dump_table_block('t1')--转储数据块execute dump_undo_block--转储undo块  <span style="color:#ff0000;">序号4</span>

       3.4.4 找到转储出来的日志文件

       序号1 执行完后

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0003.015.0000065c  0x00c009af.01e8.1c  --U-    3  fsc 0x0000.0035e07c --理论上，这里的标记应该被清除了0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000bdba: 0x00416169data_block_dump,data header at 0x1ce9705c

    标记没有清掉，不知道为什么

序号2 执行完后
数据块转储结果

*** 2015-01-04 22:52:48.506Start dump data blocks tsn: 0 file#:1 minblk 90473 maxblk 90473Block dump from cache:Dump of buffer cache at level 4 for tsn=0, rdba=4284777Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0003.015.0000065c  0x00c009af.01e8.1c  C---    0  scn 0x0000.0035e07c --标记被清除了0x02   0x0004.00b.00000595  0x00c02694.0109.1d  ----    1  fsc 0x0000.00000000 --锁住了修改的行bdba: 0x00416169data_block_dump,data header at 0x1ce9705c

undo块转储结果

*** 2015-01-04 22:53:40.834===================Undo Segment Header===================Start dump data blocks tsn: 2 file#:3 minblk 176 maxblk 176Block dump from cache:Dump of buffer cache at level 4 for tsn=2, rdba=12583088.....TRN TBL::   index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt  ------------------------------------------------------------------------------------------------   0x00    9    0x00  0x0593  0x000c  0x0000.0035df6c  0x00c02693  0x0000.000.00000000  0x00000002   0x00000000  1420382305   0x01    9    0x00  0x056d  0x001e  0x0000.0035dcd1  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381360   0x02    9    0x00  0x0591  0x0005  0x0000.0035de9e  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381960   0x03    9    0x00  0x0594  0x0018  0x0000.0035e16f  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382905   0x04    9    0x00  0x0594  0x001f  0x0000.0035e189  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382905   0x05    9    0x00  0x0594  0x0009  0x0000.0035deed  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420382080   0x06    9    0x00  0x0594  0x0019  0x0000.0035e080  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382629   0x07    9    0x00  0x0593  0x0002  0x0000.0035de5e  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381815   0x08    9    0x00  0x0594  0x000a  0x0000.0035e089  0x00c02693  0x0000.000.00000000  0x00000001   0x00000000  1420382629   0x09    9    0x00  0x0592  0x001d  0x0000.0035df03  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420382080   0x0a    9    0x00  0x0593  0x0011  0x0000.0035e0b8  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382639   0x0b   10    0x80  0x0595  0x0003  0x0000.00000000  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  0 <span style="color:#ff0000;">--跟数据块的事务槽对上了</span>   0x0c    9    0x00  0x0594  0x001c  0x0000.0035dfad  0x00c02693  0x0000.000.00000000  0x00000001   0x00000000  1420382440   0x0d    9    0x00  0x0592  0x0014  0x0000.0035ddac  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381705 .....*-----------------------------* Rec #0x1d  slt: 0x0b  objn: 77560(0x00012ef8)  objd: 77560  tblspc: 0(0x00000000)*       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: 0flg2: 0*-----------------------------uba: 0x00c02694.0109.1c ctl max scn: 0x0000.0035dc32 prv tx scn: 0x0000.0035dc72txn start scn: scn: 0x0000.0035e1d6 logon user: 0 prev brb: 12592785 prev bcl: 0KDO undo record:KTB Redo op: 0x03  ver: 0x01  compat bit: 4 (post-11) padding: 0op: ZKDO Op code: URP row dependencies Disabled  xtype: XA flags: 0x00000000  bdba: 0x00416169  hdba: 0x00416168itli: 2  ispac: 0  maxfr: 4863tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0ncol: 2 nnew: 1 size: -1col  1: [ 2]  c1 02<span style="color:#ff0000;">-- 这是数据块事务槽uba的地址指向的undo记录，c1 02 代表着1，说明修改前是1.</span>

序号3执行完后

数据块转储结果

*** 2015-01-04 23:06:25.105Block dump from cache:Dump of buffer cache at level 4 for tsn=0, rdba=4284777.....Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0003.015.0000065c  0x00c009af.01e8.1c  C---    0  scn 0x0000.0035e07c0x02   0x0004.00b.00000595  0x00c02694.0109.1e  ----    1  fsc 0x0000.00000000  --uba 变了，由 0x00c02694.0109.1d 变成了  0x00c02694.0109.1e</span>bdba: 0x00416169data_block_dump,data header at 0x1ce9705c.....block_row_dump:tab 0, row 0, @0x1f7btl: 10 fb: --H-FL-- lb: 0x2  cc: 2col  0: [ 2]  c1 02col  1: [ 3]  c2 02 03tab 0, row 1, @0x1f8etl: 9 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 2]  c1 03col  1: [ 2]  c1 03tab 0, row 2, @0x1f85tl: 9 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 2]  c1 04col  1: [ 2]  c1 04end_of_block_dump

undo块转储结果

*** 2015-01-04 23:06:31.347===================Undo Segment Header===================Start dump data blocks tsn: 2 file#:3 minblk 176 maxblk 176Block dump from cache:Dump of buffer cache at level 4 for tsn=2, rdba=12583088....  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt  ------------------------------------------------------------------------------------------------   0x00    9    0x00  0x0593  0x000c  0x0000.0035df6c  0x00c02693  0x0000.000.00000000  0x00000002   0x00000000  1420382305   0x01    9    0x00  0x056d  0x001e  0x0000.0035dcd1  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381360   0x02    9    0x00  0x0591  0x0005  0x0000.0035de9e  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381960   0x03    9    0x00  0x0594  0x0018  0x0000.0035e16f  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382905   0x04    9    0x00  0x0594  0x001f  0x0000.0035e189  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382905   0x05    9    0x00  0x0594  0x0009  0x0000.0035deed  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420382080   0x06    9    0x00  0x0594  0x0019  0x0000.0035e080  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382629   0x07    9    0x00  0x0593  0x0002  0x0000.0035de5e  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381815   0x08    9    0x00  0x0594  0x000a  0x0000.0035e089  0x00c02693  0x0000.000.00000000  0x00000001   0x00000000  1420382629   0x09    9    0x00  0x0592  0x001d  0x0000.0035df03  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420382080   0x0a    9    0x00  0x0593  0x0011  0x0000.0035e0b8  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382639   0x0b   10    0x80  0x0595  0x0003  0x0000.00000000  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  0   0x0c    9    0x00  0x0594  0x001c  0x0000.0035dfad  0x00c02693  0x0000.000.00000000  0x00000001   0x00000000  1420382440   0x0d    9    0x00  0x0592  0x0014  0x0000.0035ddac  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381705 .......*-----------------------------* Rec #0x1d  slt: 0x0b  objn: 77560(0x00012ef8)  objd: 77560  tblspc: 0(0x00000000)*       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: 0flg2: 0*-----------------------------uba: 0x00c02694.0109.1c ctl max scn: 0x0000.0035dc32 prv tx scn: 0x0000.0035dc72txn start scn: scn: 0x0000.0035e1d6 logon user: 0 prev brb: 12592785 prev bcl: 0KDO undo record:KTB Redo op: 0x03  ver: 0x01  compat bit: 4 (post-11) padding: 0op: ZKDO Op code: URP row dependencies Disabled  xtype: XA flags: 0x00000000  bdba: 0x00416169  hdba: 0x00416168itli: 2  ispac: 0  maxfr: 4863tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0ncol: 2 nnew: 1 size: -1col  1: [ 2]  c1 02  *-----------------------------* Rec #0x1e  slt: 0x0b  objn: 77560(0x00012ef8)  objd: 77560  tblspc: 0(0x00000000)*       Layer:  11 (Row)   opc: 1   rci 0x1d   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: 0op: C  uba: 0x00c02694.0109.1d --指向前一个undo记录KDO Op code: URP row dependencies Disabled  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x00416169  hdba: 0x00416168itli: 2  ispac: 0  maxfr: 4863tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0ncol: 2 nnew: 1 size: 0Vector content: col  1: [ 3]  c2 02 02 -- 这是数据块事务槽uba的地址指向的undo记录，c2 02 02 代表着101，说明修改前是101

序号4执行完后

数据块转储结果

*** 2015-01-04 23:13:22.306Start dump data blocks tsn: 0 file#:1 minblk 90473 maxblk 90473Block dump from cache:Dump of buffer cache at level 4 for tsn=0, rdba=4284777.... Itl           Xid                  Uba         Flag  Lck        Scn/Fsc0x01   0x0003.015.0000065c  0x00c009af.01e8.1c  C---    0  scn 0x0000.0035e07c0x02   0x0004.00b.00000595  0x00c02694.0109.1f  ----    1  fsc 0x0000.00000000 --uba 变了，由 0x00c02694.0109.1e 变成了  0x00c02694.0109.1fbdba: 0x00416169....block_row_dump:tab 0, row 0, @0x1f7btl: 10 fb: --H-FL-- lb: 0x2  cc: 2col  0: [ 2]  c1 02col  1: [ 3]  c2 02 04tab 0, row 1, @0x1f8etl: 9 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 2]  c1 03col  1: [ 2]  c1 03tab 0, row 2, @0x1f85tl: 9 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 2]  c1 04col  1: [ 2]  c1 04end_of_block_dump

undo块转储结果

*** 2015-01-04 23:13:31.622===================Undo Segment Header===================Start dump data blocks tsn: 2 file#:3 minblk 176 maxblk 176Block dump from cache:Dump of buffer cache at level 4 for tsn=2, rdba=12583088.... index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt  ------------------------------------------------------------------------------------------------   0x00    9    0x00  0x0593  0x000c  0x0000.0035df6c  0x00c02693  0x0000.000.00000000  0x00000002   0x00000000  1420382305   0x01    9    0x00  0x056d  0x001e  0x0000.0035dcd1  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381360   0x02    9    0x00  0x0591  0x0005  0x0000.0035de9e  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381960   0x03    9    0x00  0x0594  0x0018  0x0000.0035e16f  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382905   0x04    9    0x00  0x0594  0x001f  0x0000.0035e189  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382905   0x05    9    0x00  0x0594  0x0009  0x0000.0035deed  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420382080   0x06    9    0x00  0x0594  0x0019  0x0000.0035e080  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382629   0x07    9    0x00  0x0593  0x0002  0x0000.0035de5e  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381815   0x08    9    0x00  0x0594  0x000a  0x0000.0035e089  0x00c02693  0x0000.000.00000000  0x00000001   0x00000000  1420382629   0x09    9    0x00  0x0592  0x001d  0x0000.0035df03  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420382080   0x0a    9    0x00  0x0593  0x0011  0x0000.0035e0b8  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  1420382639   0x0b   10    0x80  0x0595  0x0003  0x0000.00000000  0x00c02694  0x0000.000.00000000  0x00000001   0x00000000  0   0x0c    9    0x00  0x0594  0x001c  0x0000.0035dfad  0x00c02693  0x0000.000.00000000  0x00000001   0x00000000  1420382440   0x0d    9    0x00  0x0592  0x0014  0x0000.0035ddac  0x00c02692  0x0000.000.00000000  0x00000001   0x00000000  1420381705.....*-----------------------------* Rec #0x1d  slt: 0x0b  objn: 77560(0x00012ef8)  objd: 77560  tblspc: 0(0x00000000)*       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: 0flg2: 0*-----------------------------uba: 0x00c02694.0109.1c ctl max scn: 0x0000.0035dc32 prv tx scn: 0x0000.0035dc72txn start scn: scn: 0x0000.0035e1d6 logon user: 0 prev brb: 12592785 prev bcl: 0KDO undo record:KTB Redo op: 0x03  ver: 0x01  compat bit: 4 (post-11) padding: 0op: ZKDO Op code: URP row dependencies Disabled  xtype: XA flags: 0x00000000  bdba: 0x00416169  hdba: 0x00416168itli: 2  ispac: 0  maxfr: 4863tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0ncol: 2 nnew: 1 size: -1col  1: [ 2]  c1 02 *-----------------------------* Rec #0x1e  slt: 0x0b  objn: 77560(0x00012ef8)  objd: 77560  tblspc: 0(0x00000000)*       Layer:  11 (Row)   opc: 1   rci 0x1d   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: 0op: C  uba: 0x00c02694.0109.1d--指向前一个undo记录KDO Op code: URP row dependencies Disabled  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x00416169  hdba: 0x00416168itli: 2  ispac: 0  maxfr: 4863tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0ncol: 2 nnew: 1 size: 0Vector content: col  1: [ 3]  c2 02 02 *-----------------------------* Rec #0x1f  slt: 0x0b  objn: 77560(0x00012ef8)  objd: 77560  tblspc: 0(0x00000000)*       Layer:  11 (Row)   opc: 1   rci 0x1e   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: 0op: C  uba: 0x00c02694.0109.1e --指向前一个undo记录</span>KDO Op code: URP row dependencies Disabled  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x00416169  hdba: 0x00416168itli: 2  ispac: 0  maxfr: 4863tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0ncol: 2 nnew: 1 size: 0Vector content: col  1: [ 3]  c2 02 03 -- 这是数据块事务槽uba的地址指向的undo记录，c2 02 03代表着102，说明修改前是102.