分享程序员开发的那些事...
更新时间：2022-09-13 10:32:12
[20150228]Delayed Block Cleanout 2.txt
--前几天我自己做了1次Delayed Block Cleanout的例子，我一直有一个疑问。
--链接如下：http://blog.itpub.net/267265/viewspace-1441526/
--如果我很久不查询这些块，scn会是多少呢？这个一直是我的疑问，重复测试：
1.建立测试环境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
'/u01/app/oracle11g/oradata/test/undotbs02.dbf' SIZE 4M AUTOEXTEND OFF
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;
create table small ( x int, y char(500) );
insert into small select rownum, 'x' from all_users;
commit;
exec dbms_stats.gather_table_stats( user, 'SMALL' );
--建立sql脚本test2.sql：
set verify off
begin
for i in 1 .. 5000
loop
update small set y = i where x= &1;
commit;
end loop;
end;
/
exit
--建立shell脚本如下test2.sh：
sqlplus -s scott/btbtms @test2 1 &
sqlplus -s scott/btbtms @test2 2 &
sqlplus -s scott/btbtms @test2 3 &
sqlplus -s scott/btbtms @test2 4 &
sqlplus -s scott/btbtms @test2 5 &
sqlplus -s scott/btbtms @test2 6 &
sqlplus -s scott/btbtms @test2 7 &
sqlplus -s scott/btbtms @test2 8 &
sqlplus -s scott/btbtms @test2 9 &
2.开始测试：
alter system set undo_tablespace = UNDOTBS2;
SCOTT@test> update dept set loc=UPPER(loc) ;
7 rows updated.
SCOTT@test> alter system flush BUFFER_CACHE ;
System altered.
--我已经转储了数据缓冲。
SCOTT@test> alter system checkpoint ;
System altered.
SCOTT@test> select current_scn from v$database ;
CURRENT_SCN
------------
12004525127
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select current_scn from v$database ;
CURRENT_SCN
------------
12004525132
--这样修改的数据块scn在 12004525127-12004525132之间。
SCOTT@test> select ora_rowscn,dept.* from dept where rownum=1;
ORA_ROWSCN DEPTNO DNAME LOC
------------ ------------ -------------- -------------
12004525130 10 ACCOUNTING NEW YORK
--可以发现结果是正确的。这个情况是对应的undo信息还存在,写入scn的信息是正确的。
3.重复上面的测试：
SCOTT@test> update dept set loc=UPPER(loc) ;
7 rows updated.
SCOTT@test> alter system flush BUFFER_CACHE ;
System altered.
SCOTT@test> alter system checkpoint ;
System altered.
SCOTT@test> select current_scn from v$database ;
CURRENT_SCN
------------
12004525447
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select current_scn from v$database ;
CURRENT_SCN
------------
12004525468
--这样修改的数据块scn在 12004525447-12004525468之间。如果对应undo被覆盖会出现什么情况呢？
SCOTT@test> variable x refcursor ;
SCOTT@test> exec open :x for select * from dept ;
PL/SQL procedure successfully completed.
$ ./test2.sh
--等待结束.我保险执行了2次。
SCOTT@test> set serveroutput on format wrapped
SCOTT@test> print x
ERROR:
ORA-01555: snapshot too old: rollback segment number 240 with name "_SYSSMU240_2447059684$" too small
no rows selected
--可以发现相关的undo信息被覆盖，为了保证一致性读取报错,出现ora-01555.
SCOTT@test> select ora_rowscn,dept.* from dept where rownum=1;
ORA_ROWSCN DEPTNO DNAME LOC
------------ ------------ -------------- -------------
12004775909 10 ACCOUNTING NEW YORK
-- 很明显scn=12004775909 不在12004525447-12004525468之间。
4.这个scn=12004775909来源那里呢？
SCOTT@test> alter system dump undo header "_SYSSMU240_2447059684$";
System altered.
SCOTT@test> @10to16 12004775909
10 to 16 HEX REVERSE16
-------------- -----------------------------------
00002cb8a57e5 0xe5578acb-02000000
********************************************************************************
Undo Segment: _SYSSMU240_2447059684$ (240)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 15
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x0280013c ext#: 1 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 1
Unlocked
Map Header:: next 0x00000000 #extents: 2 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x02800131 length: 7
0x02800138 length: 8
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1425114056
Extent Number:1 Commit Time: 1425114056
TRN CTL:: seq: 0x0191 chd: 0x001c ctl: 0x0016 inc: 0x00000000 nfb: 0x0000
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x0280013b.0191.03 scn: 0x0002.cb8a57e5
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0191.04 ext: 0x1 spc: 0x1dbe
uba: 0x00000000.0191.02 ext: 0x1 spc: 0x1f06
uba: 0x00000000.00a1.0b ext: 0x1 spc: 0x288
uba: 0x00000000.00a1.0b ext: 0x1 spc: 0x288
uba: 0x00000000.00a1.0b ext: 0x1 spc: 0x288
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x02b2 0x0001 0x0002.cb8a5959 0x02800132 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x01 9 0x00 0x02b2 0x0002 0x0002.cb8a598f 0x02800133 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x02 9 0x00 0x02b2 0x0005 0x0002.cb8a59d7 0x02800133 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x03 9 0x00 0x02b2 0x0007 0x0002.cb8a5a54 0x02800133 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x04 9 0x00 0x02b2 0x000a 0x0002.cb8a5b7d 0x02800134 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x05 9 0x00 0x02b2 0x0003 0x0002.cb8a5a1f 0x02800133 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x06 9 0x00 0x02b2 0x0008 0x0002.cb8a5ad3 0x02800133 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x07 9 0x00 0x02b2 0x0006 0x0002.cb8a5aa6 0x02800133 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x08 9 0x00 0x02b2 0x0009 0x0002.cb8a5b34 0x02800134 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x09 9 0x00 0x02b2 0x0004 0x0002.cb8a5b5a 0x02800134 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x0a 9 0x00 0x02b2 0x000b 0x0002.cb8a5bad 0x02800134 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x0b 9 0x00 0x02b2 0x000c 0x0002.cb8a5bd1 0x02800134 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x0c 9 0x00 0x02b2 0x000d 0x0002.cb8a5c58 0x02800134 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x0d 9 0x00 0x02b2 0x000f 0x0002.cb8a5d13 0x02800135 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x0e 9 0x00 0x02b2 0x0010 0x0002.cb8a5de6 0x02800135 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x0f 9 0x00 0x02b2 0x000e 0x0002.cb8a5dbb 0x02800135 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x10 9 0x00 0x02b2 0x0013 0x0002.cb8a5e9d 0x02800135 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x11 9 0x00 0x02b2 0x0014 0x0002.cb8a5f6c 0x02800135 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x12 9 0x00 0x02b2 0x0015 0x0002.cb8a6048 0x02800136 0x0000.000.00000000 0x00000001 0x00000000 1425114023
0x13 9 0x00 0x02b2 0x0011 0x0002.cb8a5f04 0x02800135 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x14 9 0x00 0x02b2 0x0012 0x0002.cb8a5fee 0x02800136 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x15 9 0x00 0x02b2 0x0018 0x0002.cb8a6079 0x02800136 0x0000.000.00000000 0x00000001 0x00000000 1425114023
0x16 9 0x00 0x02b2 0xffff 0x0002.cb8a61dc 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1425114056
0x17 9 0x00 0x02b1 0x001d 0x0002.cb8a587b 0x02800132 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x18 9 0x00 0x02b2 0x001b 0x0002.cb8a6135 0x02800136 0x0000.000.00000000 0x00000001 0x00000000 1425114023
0x19 9 0x00 0x02b2 0x001a 0x0002.cb8a61d1 0x0280013b 0x0000.000.00000000 0x00000003 0x00000000 1425114055
0x1a 9 0x00 0x02b2 0x001f 0x0002.cb8a61d6 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1425114055
0x1b 9 0x00 0x02b2 0x0019 0x0002.cb8a61cf 0x0280013a 0x0000.000.00000000 0x00000001 0x00000000 1425114055
0x1c 9 0x00 0x02b1 0x001e 0x0002.cb8a581f 0x02800131 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x1d 9 0x00 0x02b1 0x0021 0x0002.cb8a58b8 0x02800132 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x1e 9 0x00 0x02b1 0x0017 0x0002.cb8a584b 0x02800132 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x1f 9 0x00 0x02b2 0x0016 0x0002.cb8a61d8 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1425114056
0x20 9 0x00 0x02b1 0x0000 0x0002.cb8a5942 0x02800132 0x0000.000.00000000 0x00000001 0x00000000 1425114022
0x21 9 0x00 0x02b1 0x0020 0x0002.cb8a58f2 0x02800132 0x0000.000.00000000 0x00000001 0x00000000 1425114022
EXT TRN CTL::
usn: 240
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
sp5:0x00000000 sp6:0x7f00000000 sp7:0x00000000 sp8:0x00000000
EXT TRN TBL::
index extflag extHash extSpare1 extSpare2
---------------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
0x01 0x00000000 0x00000000 0x00000000 0x00000000
0x02 0x00000000 0x00000000 0x00000000 0x00000000
0x03 0x00000000 0x00000000 0x00000000 0x00000000
0x04 0x00000000 0x00000000 0x00000000 0x00000000
0x05 0x00000000 0x00000000 0x00000000 0x00000000
0x06 0x00000000 0x00000000 0x00000000 0x00000000
0x07 0x00000000 0x00000000 0x00000000 0x00000000
0x08 0x00000000 0x00000000 0x00000000 0x00000000
0x09 0x00000000 0x00000000 0x00000000 0x00000000
0x0a 0x00000000 0x00000000 0x00000000 0x00000000
0x0b 0x00000000 0x00000000 0x00000000 0x00000000
0x0c 0x00000000 0x00000000 0x00000000 0x00000000
0x0d 0x00000000 0x00000000 0x00000000 0x00000000
0x0e 0x00000000 0x00000000 0x00000000 0x00000000
0x0f 0x00000000 0x00000000 0x00000000 0x00000000
0x10 0x00000000 0x00000000 0x00000000 0x00000000
0x11 0x00000000 0x00000000 0x00000000 0x00000000
0x12 0x00000000 0x00000000 0x00000000 0x00000000
0x13 0x00000000 0x00000000 0x00000000 0x00000000
0x14 0x00000000 0x00000000 0x00000000 0x00000000
0x15 0x00000000 0x00000000 0x00000000 0x00000000
0x16 0x00000000 0x00000000 0x00000000 0x00000000
0x17 0x00000000 0x00000000 0x00000000 0x00000000
0x18 0x00000000 0x00000000 0x00000000 0x00000000
0x19 0x00000000 0x00000000 0x00000000 0x00000000
0x1a 0x00000000 0x00000000 0x00000000 0x00000000
0x1b 0x00000000 0x00000000 0x00000000 0x00000000
0x1c 0x00000000 0x00000000 0x00000000 0x00000000
0x1d 0x00000000 0x00000000 0x00000000 0x00000000
0x1e 0x00000000 0x00000000 0x00000000 0x00000000
0x1f 0x00000000 0x00000000 0x00000000 0x00000000
0x20 0x00000000 0x00000000 0x00000000 0x00000000
0x21 0x00000000 0x00000000 0x00000000 0x00000000
--可以发现如果覆盖，从TRN CTL里面取出Scn。
TRN CTL:: seq: 0x0191 chd: 0x001c ctl: 0x0016 inc: 0x00000000 nfb: 0x0000
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x0280013b.0191.03 scn: 0x0002.cb8a57e5
~~~~~~~~~~~~~~~~~~~~~~
SCOTT@test> select rowid from dept where rownum=1;
ROWID
------------------
AABBrlAAEAAAAWDAAB
SCOTT@test> @lookup_rowid AABBrlAAEAAAAWDAAB
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
269029 4 1411 1 4,1411 alter system dump datafile 4 block 1411
SCOTT@test> alter system dump datafile 4 block 1411;
System altered.
Block header dump: 0x01000583
Object id on Block? Y
seg/obj: 0x41ae5 csc: 0x02.cb8a620f itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000580 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x00f3.006.00000371 0x0280016a.01eb.21 C--- 0 scn 0x0002.cb86844a
0x02 0x00f0.01d.00000259 0x0280013d.014d.04 C-U- 0 scn 0x0002.cb8a57e5
0x03 0x0068.001.00000317 0x028000b1.01e1.07 C-U- 0 scn 0x0002.cb80985e
bdba: 0x01000583
data_block_dump,data header at 0x2a97149a7c