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