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

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

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