看了世界杯第一场,想睡睡不着,起来想看第二场,又被BITI大师的问题给难住了,没心思看了,自己做测试了
SQL> create table testitl ( name varchar2(10) ) ;
Table created.
SQL> insert into testitl values ('sun') ;
1 row created.
SQL> c/sun/rong/
1* insert into testitl values ('rong')
SQL> /
1 row created.
SQL> c/rong/chen/
1* insert into testitl values ('chen')
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid),
2 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from testitl ;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 386
4 386
4 386
SQL> alter system dump datafile 4 block 386 ;
System altered.
-------DUMP前后物理文件386块中都没有数据,说明DUMP信息也是从BUFFER来的
----------------------------------------------
以下是TRACE文件信息(省略无关部分)
Itl Xid Uba Flag LckScn/Fsc
0x01 0x0005.009.000001e6 0x0080015b.001a.08 --U- 3 fsc 0x0000.00026c14
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xaa86e5c
===============
tsiz: 0x7a0
hsiz: 0x18
pbl: 0x0aa86e5c
bdba: 0x01000182
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x789
avsp=0x76d
tosp=0x76d
0xe
ti[0] nrow=3 offs=0
0x12
ri[0] offs=0x799
0x14
ri[1] offs=0x791
0x16
ri[2] offs=0x789
block_row_dump:
tab 0, row 0, @0x799
tl: 7 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 3] 73 75 6e
tab 0, row 1, @0x791
tl: 8 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 4] 72 6f 6e 67
tab 0, row 2, @0x789
tl: 8 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 4] 63 68 65 6e
end_of_block_dump
End dump data blocks tsn: 3 file#: 4 minblk 386 maxblk 386
-------------------------------------------------------------
SQL> alter system checkpoint ;
System altered.
--------------------------------------------------------------
接下来看一下数据文件DUMP HEX内容
block=[386]
--------------------Debug package iLen=[2048]--------------------
0000 06 02 00 00 82 01 00 01 14 6C 02 00 00 00 01 06 .........l......
0016 D5 E0 00 00 01 00 00 00 FF 16 00 00 0E 6C 02 00 .............l..
0032 00 00 00 00 02 00 03 00 00 00 00 00 05 00 09 00 ................----02 00表示ITL数,offset 44到91为ITL信息
0048 E6 01 00 00 5B 01 80 00 1A 00 08 00 03 20 00 00 ....[...........
0064 14 6C 02 00 00 00 00 00 00 00 00 00 00 00 00 00 .l..............
0080 00 00 00 00 00 00 00 00 00 00 00 00 00 01 03 00 ................
0096 FF FF 18 00 89 07 6D 07 6D 07 00 00 03 00 99 07 ......m.m....... ----offset 110到115是row directory
0112 91 07 89 07 00 00 00 00 00 00 00 00 00 00 00 00 ................
0128 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
................中间都为0省略
2016 00 00 00 00 00 2C 01 01 04 63 68 65 6E 2C 01 01 .....,...chen,.. ------这些是行数据
2032 04 72 6F 6E 67 2C 01 01 03 73 75 6E 01 06 14 6C .rong,...sun...l
开三个窗口分别更新三条记录且不提交
session 1:
SQL> update testitl set name='1' where name='sun' ;
1 row updated.
session 2:
SQL>update testitl set name='2' where name='rong' ;
1 row updated.
session 3:
SQL> update testitl set name='3' where name='chen' ;
1 row updated.
SQL> alter system dump datafile 4 block 386 ;
System altered.
-----------------------------------------------------------
以下是更新后的TRACE文件:
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.006.000001e6 0x0080863e.0017.04 ---- 1 fsc 0x0000.00000000
0x02 0x0009.001.000001ed 0x00808e3f.0019.13 ---- 1 fsc 0x0000.00000000
0x03 0x0006.004.000001f4 0x0080903d.0018.12 ---- 1 fsc 0x0000.00000000 ----ITL增加
data_block_dump,data header at 0xaa86e74
===============
tsiz: 0x788
hsiz: 0x18
pbl: 0x0aa86e74
bdba: 0x01000182
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x762
avsp=0x755
tosp=0x755
0xe
ti[0] nrow=3 offs=0
0x12
ri[0] offs=0x76c
0x14
ri[1] offs=0x767
0x16
ri[2] offs=0x762
block_row_dump:
tab 0, row 0, @0x76c
tl: 5 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 1] 31
tab 0, row 1, @0x767
tl: 5 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 1] 32
tab 0, row 2, @0x762
tl: 5 fb: --H-FL-- lb: 0x3 cc: 1
col 0: [ 1] 33
end_of_block_dump
End dump data blocks tsn: 3 file#: 4 minblk 386 maxblk 386
-------------------------------------------------------------
SQL> alter system checkpoint ;
System altered.
--------------------------------------------------------------
再看一下数据文件DUMP HEX内容
0000 06 02 00 00 82 01 00 01 13 70 02 00 00 00 01 04 .........p......
0016 A1 35 00 00 01 00 00 00 FF 16 00 00 12 70 02 00 .5...........p..
0032 00 00 00 00 03 00 03 00 00 00 00 00 02 00 06 00 ................ ----------这里是三条ITL信息
0048 E6 01 00 00 3E 86 80 00 17 00 04 00 01 00 00 00 ....>...........
0064 00 00 00 00 09 00 01 00 ED 01 00 00 3F 8E 80 00 ............?...
0080 19 00 13 00 01 00 00 00 00 00 00 00 06 00 04 00 ................
0096 F4 01 00 00 3D 90 80 00 18 00 12 00 01 00 00 00 ....=...........
0112 00 00 00 00 00 01 03 00 FF FF 18 00 62 07 55 07 ............b.U.
0128 55 07 00 00 03 00 6C 07 67 07 62 07 00 00 00 00 U.....l.g.b..... -----offset 134到139是row directory
.............中间都为0省略
2000 00 00 00 00 00 00 2C 03 01 01 33 2C 01 01 01 32 ......,...3,...2 ----------看来数据确实上移了,且原来的数据还是在的
2016 2C 02 01 01 31 2C 00 01 04 63 68 65 6E 2C 00 01 ,...1,...chen,..
2032 04 72 6F 6E 67 2C 00 01 03 73 75 6E 01 06 13 70 .rong,...sun...p
结论:我错了,还自己动手做一下好,又了解了些东西
以前以为数据更新应该是在数据原来位置进行的,没想到啊.想不通为什么ORACLE要这样做呢,长度比原来都短,为何不在原来位置进行更新?
继续实验,继续插入数据直到块满,最后这部分空间也没被利用,疑惑......