程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle事務原理探究2--讀書筆記五

Oracle事務原理探究2--讀書筆記五

編輯:Oracle教程

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 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);

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')

 

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

3.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.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/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
標記沒有清掉,不知道為什麼

序號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 0x1ce9705c


undo塊轉儲結果

 

 

*** 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_dump
undo塊轉儲結果
*** 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_dump
undo塊轉儲結果

 

*** 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>






 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved