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;
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/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 90473
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;
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;
創建一個轉儲undo塊的存儲過程
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塊序號4
3.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標記沒有清掉,不知道為什麼
*** 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>
序號3執行完後
數據塊轉儲結果
*** 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>