首页 > 代码库 > Oracle事务原理探究2--读书笔记五
Oracle事务原理探究2--读书笔记五
续上篇...
3. 数据块访问与undo
任何时候当会话查看一个数据块的时候，都需要保证看到的是适当的数据版本。从外部观点来看，这意味着会话不应该看到任何未提交的数据，或许还不应该看到查询开始后修改并提交的数据（取决于事务的隔离级别），这称为数据的读一致性版本。
下面我们来谈谈oracle怎么实现读一致性版本的：
3.1 设置场景
我们会创建一张表，里面插入3条数据，然后开几个会话对其进行操作
3.1.1 连接oracle，以任何一个用户登录即可，初始化一些格式
execute dbms_random.seed(0) set doc off doc end doc is marked with # # set linesize 120 set trimspool on set pagesize 24 set arraysize 25 -- set longchunksize 32768 -- set long 32768 set autotrace off clear breaks ttitle off btitle off column owner format a15 column segment_name format a20 column table_name format a20 column index_name format a20 column object_name format a20 column subobject_name format a20 column partition_name format a20 column subpartition_name format a20 column column_name format a20 column column_expression format a40 word wrap column constraint_name format a20 column referenced_name format a30 column file_name format a60 column low_value format a24 column high_value format a24 column parent_id_plus_exp format 999 column id_plus_exp format 990 column plan_plus_exp format a90 column object_node_plus_exp format a14 column other_plus_exp format a90 column other_tag_plus_exp format a29 column access_predicates format a80 column filter_predicates format a80 column projection format a80 column remarks format a80 column partition_start format a12 column partition_stop format a12 column partition_id format 999 column other_tag format a32 column object_alias format a24 column object_node format a13 column other format a150 column os_username format a30 column terminal format a24 column userhost format a24 column client_id format a24 column statistic_name format a35 column namespace format a20 column attribute format a20 column hint format a40 column start_time format a25 column end_time format a25 column time_now noprint new_value m_timestamp set feedback off select to_char(sysdate,'hh24miss') time_now from dual; commit; set feedback on set timing off set verify off alter session set optimizer_mode = all_rows; spool log3.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); begin dbms_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_name in varchar2,
i_owner in varchar2 default sys_context('userenv','session_user')
)
as
m_file_id number;
m_block number;
m_process varchar2(32);
begin
execute immediate
' select ' ||
' dbms_rowid.rowid_relative_fno(rowid), ' ||
' dbms_rowid.rowid_block_number(rowid) ' ||
' from ' ||
i_owner ||
'.' ||
i_tab_name ||
' where ' ||
' rownum = 1 '
into
m_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
--
select
spid
into
m_process
from
v$session se,
v$process pr
where
--
-- 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)
and pr.addr = se.paddr
;
dbms_output.new_line;
dbms_output.put_line('Trace file name includes: ' || m_process);
dbms_output.new_line;
exception
when others then
dbms_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 errors
drop 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')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/Fsc
0x01 0x0002.005.000005f5 0x00c00b18.0121.0d --U- 3 fsc 0x0000.00326fb8
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x00416169
data_block_dump,data header at 0x1e256e5c
===============
tsiz: 0x1fa0
hsiz: 0x18
pbl: 0x1e256e5c
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f85
avsp=0x1f6d
tosp=0x1f6d
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f97
0x14:pri[1] offs=0x1f8e
0x16:pri[2] offs=0x1f85
block_row_dump:
tab 0, row 0, @0x1f97
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 2] c1 02
tab 0, row 1, @0x1f8e
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 2] c1 03
tab 0, row 2, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 2] c1 04
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 90473 maxblk 90473
Start dump data blocks tsn: 0 file#:1 minblk 90473 maxblk 904733.2 事务列表
这一节，简要介绍一下转储出来的数据块中事务槽的信息，上面数据块的事务槽如下：
Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.005.000005f5 0x00c00b18.0121.0d --U- 3 fsc 0x0000.00326fb8 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000Itl：列表的数组索引，该值未真正存储在数据块中，它由执行转储的代码生成。该值用在行的锁字节(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/Fsc 0x01 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: 0x00416169 data_block_dump,data header at 0x1cd0825c =============== tsiz: 0x1fa0 hsiz: 0x18 pbl: 0x1cd0825c 76543210 flag=-------- ntab=1 nrow=3 frre=-1 fsbo=0x18 fseo=0x1f71 avsp=0x1f6b tosp=0x1f6b block_row_dump: tab 0, row 0, @0x1f7b tl: 10 fb: --H-FL-- lb: 0x2 cc: 2 -- 事务0x2似乎锁住了这一行，其实是锁住了这一行 col 0: [ 2] c1 02 col 1: [ 3] c2 02 02 tab 0, row 1, @0x1f71 tl: 10 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 03 col 1: [ 3] c2 02 03 tab 0, row 2, @0x1f85 tl: 9 fb: --H-FL-- lb: 0x1 cc: 2 -- 事务0x1似乎锁住了这一行，其实没有锁住，只是锁标记没有清除 col 0: [ 2] c1 04 col 1: [ 2] c1 64 end_of_block_dump执行查询之后，转储数据块结果(需要先执行alter system checkpoint;)
Itl Xid Uba Flag Lck Scn/Fsc 0x01 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: 0x00416169 data_block_dump,data header at 0x1cd0825c =============== tsiz: 0x1fa0 hsiz: 0x18 pbl: 0x1cd0825c 76543210 flag=-------- ntab=1 nrow=3 frre=-1 fsbo=0x18 fseo=0x1f71 avsp=0x1f6b tosp=0x1f6b block_row_dump: tab 0, row 0, @0x1f7b tl: 10 fb: --H-FL-- lb: 0x2 cc: 2 -- 事务0x2似乎锁住了这一行，其实是锁住了这一行。 col 0: [ 2] c1 02 col 1: [ 3] c2 02 02 tab 0, row 1, @0x1f71 tl: 10 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 03 col 1: [ 3] c2 02 03 tab 0, row 2, @0x1f85 tl: 9 fb: --H-FL-- lb: 0x0 cc: 2 --锁标记已经被清除了。 col 0: [ 2] c1 04 col 1: [ 2] c1 64 end_of_block_dump
</pre><p><span style="font-size: 14px; "><strong>3.4 真实的查询操作</strong></span></p><p> 我们知道，如果我们需要查询一个数据，假设我们是事务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的呢。下面让我们通过一个小例子来解释这种现象：</p><p> 其实，就是讲前面的步骤在执行一下，原谅我直接将上面的命令再拷贝一部分放在下面：（sys用户登录）</p><p> <span style="font-size:10px;">3.4.1 准备工作</span>
execute dbms_random.seed(0)
set doc off
doc
end doc is marked with #
#
set linesize 120
set trimspool on
set pagesize 24
set arraysize 25
-- set longchunksize 32768
-- set long 32768
set autotrace off
clear breaks
ttitle off
btitle off
column owner format a15
column segment_name format a20
column table_name format a20
column index_name format a20
column object_name format a20
column subobject_name format a20
column partition_name format a20
column subpartition_name format a20
column column_name format a20
column column_expression format a40 word wrap
column constraint_name format a20
column referenced_name format a30
column file_name format a60
column low_value format a24
column high_value format a24
column parent_id_plus_exp format 999
column id_plus_exp format 990
column plan_plus_exp format a90
column object_node_plus_exp format a14
column other_plus_exp format a90
column other_tag_plus_exp format a29
column access_predicates format a80
column filter_predicates format a80
column projection format a80
column remarks format a80
column partition_start format a12
column partition_stop format a12
column partition_id format 999
column other_tag format a32
column object_alias format a24
column object_node format a13
column other format a150
column os_username format a30
column terminal format a24
column userhost format a24
column client_id format a24
column statistic_name format a35
column namespace format a20
column attribute format a20
column hint format a40
column start_time format a25
column end_time format a25
column time_now noprint new_value m_timestamp
set feedback off
select to_char(sysdate,'hh24miss') time_now from dual;
commit;
set feedback on
set timing off
set verify off
alter 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);
begin
dbms_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_name in varchar2,
i_owner in varchar2 default sys_context('userenv','session_user')
)
as
m_file_id number;
m_block number;
m_process varchar2(32);
begin
execute immediate
' select ' ||
' dbms_rowid.rowid_relative_fno(rowid), ' ||
' dbms_rowid.rowid_block_number(rowid) ' ||
' from ' ||
i_owner ||
'.' ||
i_tab_name ||
' where ' ||
' rownum = 1 '
into
m_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
--
select
spid
into
m_process
from
v$session se,
v$process pr
where
--
-- 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)
and pr.addr = se.paddr
;
dbms_output.new_line;
dbms_output.put_line('Trace file name includes: ' || m_process);
dbms_output.new_line;
exception
when others then
dbms_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 errors
drop public synonym dump_table_block;
create public synonym dump_table_block for dump_table_block;
grant execute on dump_table_block to public;create or replace procedure dump_undo_block
as
m_xidusn number;
m_header_file_id number;
m_header_block_id number;
m_start_file_id number;
m_start_block_id number;
m_file_id number;
m_block_id number;
m_process number;
begin
select
xidusn,
start_ubafil,
start_ubablk,
ubafil,
ubablk
into
m_xidusn,
m_start_file_id,
m_start_block_id,
m_file_id,
m_block_id
from
v$session ses,
v$transaction trx
where
ses.sid = (select mys.sid from V$mystat mys where rownum = 1)
and trx.ses_addr = ses.saddr
;
select
file_id, block_id
into
m_header_file_id,
m_header_block_id
from
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 then
dbms_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;
select
spid
into
m_process
from
v$session se,
v$process pr
where se.sid = (select sid from v$mystat where rownum = 1)
and
pr.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;--清除标记 <pre name="code" class="sql">alter system checkpoint;--刷新输出磁盘，执行完等5,6s
execute 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块序号2update t1 set n1=102 where id=1;--第二次更新alter system checkpoint;--刷新输出磁盘，执行完等5,6sexecute dump_table_block(‘t1‘)--转储数据块execute dump_undo_block--转储undo块序号3update t1 set n1=103 where id=1;--第三次更新alter system checkpoint;--刷新输出磁盘，执行完等5,6sexecute dump_table_block(‘t1‘)--转储数据块execute dump_undo_block--转储undo块序号43.4.4 找到转储出来的日志文件
序号1 执行完后
Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0003.015.0000065c 0x00c009af.01e8.1c --U- 3 fsc 0x0000.0035e07c --理论上，这里的标记应该被清除了 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 bdba: 0x00416169 data_block_dump,data header at 0x1ce9705c标记没有清掉，不知道为什么
序号2 执行完后
数据块转储结果
*** 2015-01-04 22:52:48.506 Start dump data blocks tsn: 0 file#:1 minblk 90473 maxblk 90473 Block dump from cache: Dump of buffer cache at level 4 for tsn=0, rdba=4284777 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0003.015.0000065c 0x00c009af.01e8.1c C--- 0 scn 0x0000.0035e07c --标记被清除了 0x02 0x0004.00b.00000595 0x00c02694.0109.1d ---- 1 fsc 0x0000.00000000 --锁住了修改的行 bdba: 0x00416169 data_block_dump,data header at 0x1ce9705cundo块转储结果
*** 2015-01-04 22:53:40.834 =================== Undo Segment Header =================== Start dump data blocks tsn: 2 file#:3 minblk 176 maxblk 176 Block 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: 0 flg2: 0 *----------------------------- uba: 0x00c02694.0109.1c ctl max scn: 0x0000.0035dc32 prv tx scn: 0x0000.0035dc72 txn start scn: scn: 0x0000.0035e1d6 logon user: 0 prev brb: 12592785 prev bcl: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 compat bit: 4 (post-11) padding: 0 op: Z KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00416169 hdba: 0x00416168 itli: 2 ispac: 0 maxfr: 4863 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0 ncol: 2 nnew: 1 size: -1 col 1: [ 2] c1 02<span style="color:#ff0000;">-- 这是数据块事务槽uba的地址指向的undo记录，c1 02 代表着1，说明修改前是1.</span>
数据块转储结果
*** 2015-01-04 23:06:25.105 Block dump from cache: Dump of buffer cache at level 4 for tsn=0, rdba=4284777 ..... Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0003.015.0000065c 0x00c009af.01e8.1c C--- 0 scn 0x0000.0035e07c 0x02 0x0004.00b.00000595 0x00c02694.0109.1e ---- 1 fsc 0x0000.00000000 <span style="color:#ff0000;"> --uba 变了，由 <span style="font-family: Arial, Helvetica, sans-serif;">0x00c02694.0109.1d 变成了 </span><span style="font-family: Arial, Helvetica, sans-serif;">0x00c02694.0109.1e</span></span><span style="font-family: Arial, Helvetica, sans-serif;"> </span>bdba: 0x00416169 data_block_dump,data header at 0x1ce9705c ..... block_row_dump: tab 0, row 0, @0x1f7b tl: 10 fb: --H-FL-- lb: 0x2 cc: 2 col 0: [ 2] c1 02 col 1: [ 3] c2 02 03 tab 0, row 1, @0x1f8e tl: 9 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 03 col 1: [ 2] c1 03 tab 0, row 2, @0x1f85 tl: 9 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 04 col 1: [ 2] c1 04 end_of_block_dumpundo块转储结果
*** 2015-01-04 23:06:31.347 =================== Undo Segment Header =================== Start dump data blocks tsn: 2 file#:3 minblk 176 maxblk 176 Block 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: 0 flg2: 0 *----------------------------- uba: 0x00c02694.0109.1c ctl max scn: 0x0000.0035dc32 prv tx scn: 0x0000.0035dc72 txn start scn: scn: 0x0000.0035e1d6 logon user: 0 prev brb: 12592785 prev bcl: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 compat bit: 4 (post-11) padding: 0 op: Z KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00416169 hdba: 0x00416168 itli: 2 ispac: 0 maxfr: 4863 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0 ncol: 2 nnew: 1 size: -1 col 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: 0 op: C <span style="color:#ff0000;">uba: 0x00c02694.0109.1d --指向前一个undo记录</span> KDO Op code: URP row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00416169 hdba: 0x00416168 itli: 2 ispac: 0 maxfr: 4863 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0 ncol: 2 nnew: 1 size: 0 Vector content: col 1: [ 3] c2 02 02 <span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif;">-- 这是数据块事务槽uba的地址指向的undo记录，c2 02 02 代表着101，说明修改前是101.</span>
序号4执行完后
数据块转储结果
*** 2015-01-04 23:13:22.306 Start dump data blocks tsn: 0 file#:1 minblk 90473 maxblk 90473 Block dump from cache: Dump of buffer cache at level 4 for tsn=0, rdba=4284777 .... Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0003.015.0000065c 0x00c009af.01e8.1c C--- 0 scn 0x0000.0035e07c 0x02 0x0004.00b.00000595 0x00c02694.0109.1f ---- 1 fsc 0x0000.00000000 <span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif;">--uba 变了，由 </span><span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif;">0x00c02694.0109.1e 变成了 </span><span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif;">0x00c02694.0109.1f</span> bdba: 0x00416169 .... block_row_dump: tab 0, row 0, @0x1f7b tl: 10 fb: --H-FL-- lb: 0x2 cc: 2 col 0: [ 2] c1 02 col 1: [ 3] c2 02 04 tab 0, row 1, @0x1f8e tl: 9 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 03 col 1: [ 2] c1 03 tab 0, row 2, @0x1f85 tl: 9 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [ 2] c1 04 col 1: [ 2] c1 04 end_of_block_dumpundo块转储结果
*** 2015-01-04 23:13:31.622 =================== Undo Segment Header =================== Start dump data blocks tsn: 2 file#:3 minblk 176 maxblk 176 Block 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: 0 flg2: 0 *----------------------------- uba: 0x00c02694.0109.1c ctl max scn: 0x0000.0035dc32 prv tx scn: 0x0000.0035dc72 txn start scn: scn: 0x0000.0035e1d6 logon user: 0 prev brb: 12592785 prev bcl: 0 KDO undo record: KTB Redo op: 0x03 ver: 0x01 compat bit: 4 (post-11) padding: 0 op: Z KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x00416169 hdba: 0x00416168 itli: 2 ispac: 0 maxfr: 4863 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0 ncol: 2 nnew: 1 size: -1 col 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: 0 op: C <span style="color:#ff0000;">uba: 0x00c02694.0109.1d<span style="font-family: Arial, Helvetica, sans-serif;">-</span></span><span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif;">-指向前一个undo记录</span> KDO Op code: URP row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00416169 hdba: 0x00416168 itli: 2 ispac: 0 maxfr: 4863 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0 ncol: 2 nnew: 1 size: 0 Vector 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: 0 op: C <span style="color:#ff0000;">uba: 0x00c02694.0109.1e </span><span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif;">-</span><span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif;">-指向前一个undo记录</span><span style="color:#ff0000;"> </span>KDO Op code: URP row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00416169 hdba: 0x00416168 itli: 2 ispac: 0 maxfr: 4863 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0 ncol: 2 nnew: 1 size: 0 Vector content: col 1: [ 3] c2 02 03 <span style="color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif;">-- 这是数据块事务槽uba的地址指向的undo记录，c2 02 03代表着102，说明修改前是102.</span>
Oracle事务原理探究2--读书笔记五
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。