问题:
1、Oracle DataBlock是如何存放数据的
2、当发生Update导致记录变大时,Oracle是如何保证Rowid不变的
3、发生行迁移和行连接的条件是什么
4、Update后留下的空间是如何被再次利用的
构造测试环境
create
table MILIATEST
(
ID NUMBER( 10 ),
NAME VARCHAR2( 2000 ),
ADRESS VARCHAR2( 2000)
) tablespace test;
SQL> select b.tablespace_name,b.block_size,b.segment_space_management from dba_tablespaces b where tablespace_name= 'TEST' ;
TABLESPACE_NAME BLOCK_SIZE SEGMENT_SPACE_MANAGEMENT
------------------------------ ---------- ------------------------
TEST 8192 MANUAL 手动管理表空间
SQL> select a.pct_free,a.pct_used from all_tables a where a.table_name= 'MILIATEST' ;
PCT_FREE PCT_USED
---------- ----------
10 40
SQL> set serverout on;
SQL> exec show_space( 'MILIATEST' );
Free Blocks............................. 0
Total Blocks............................ 8
Total Bytes............................. 65536
Unused Blocks........................... 7
Unused Bytes............................ 57344
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 33
Last Used Block......................... 1
SQL> alter system dump datafile 6 block 33 ;
System altered
可以看到 MILIATEST 段,占用的 8 块 block , 第 33 块 block 存放了段的一些信息
Start dump data blocks tsn: 7 file#: 6 minblk 33 maxblk 33
buffer tsn: 7 rdba: 0 x01800021 ( 6 / 33 )
scn: 0 x0000. 002169 b6 seq: 0 x01 flg: 0 x04 tail: 0 x69b61001
frmt: 0 x02 chkval: 0 x289a type: 0 x10=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st= 0 , typ_found= 1
Dump of memory from 0 x090C8400 to 0 x090CA400
90 C 8400 0000 A 210 01800021 002169 B6 04010000 [....!....i!.....]
90 C 8410 0000289 A 00000000 00000000 00000000 [.(..............]
90 C 8420 00000000 00000001 00000007 00001020 [............ ...]
90 C 8430 00000000 00000000 00000007 01800022 [............"...]
90C8440 00000000 00000000 00000000 00000000 [................]
90C8450 00000000 00000000 00000000 00000001 [................]
90C8460 00000000 0000CF2C 40000000 01800022 [....,......@"...]
90 C 8470 00000007 00000000 00000000 00000000 [................]
90 C 8480 00000000 00000000 00000000 00000000 [................]
Repeat 250 times
90 C 9430 00000000 00010000 00010001 00000000 [................]
90 C 9440 00000000 00000000 00000000 00000000 [................]
Repeat 250 times
90 CA3F0 00000000 00000000 00000000 69 B61001 [...............i]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7
last map 0 x00000000 #maps: 0 offset: 4128
Highwater:: 0x01800022 ext#: 0 blk#: 0 ext size: 7
#blocks in seg. hdr 's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 53036 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x01800022 length: 7
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
(
ID NUMBER( 10 ),
NAME VARCHAR2( 2000 ),
ADRESS VARCHAR2( 2000)
) tablespace test;
SQL> select b.tablespace_name,b.block_size,b.segment_space_management from dba_tablespaces b where tablespace_name= 'TEST' ;
TABLESPACE_NAME BLOCK_SIZE SEGMENT_SPACE_MANAGEMENT
------------------------------ ---------- ------------------------
TEST 8192 MANUAL 手动管理表空间
SQL> select a.pct_free,a.pct_used from all_tables a where a.table_name= 'MILIATEST' ;
PCT_FREE PCT_USED
---------- ----------
10 40
SQL> set serverout on;
SQL> exec show_space( 'MILIATEST' );
Free Blocks............................. 0
Total Blocks............................ 8
Total Bytes............................. 65536
Unused Blocks........................... 7
Unused Bytes............................ 57344
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 33
Last Used Block......................... 1
SQL> alter system dump datafile 6 block 33 ;
System altered
可以看到 MILIATEST 段,占用的 8 块 block , 第 33 块 block 存放了段的一些信息
Start dump data blocks tsn: 7 file#: 6 minblk 33 maxblk 33
buffer tsn: 7 rdba: 0 x01800021 ( 6 / 33 )
scn: 0 x0000. 002169 b6 seq: 0 x01 flg: 0 x04 tail: 0 x69b61001
frmt: 0 x02 chkval: 0 x289a type: 0 x10=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st= 0 , typ_found= 1
Dump of memory from 0 x090C8400 to 0 x090CA400
90 C 8400 0000 A 210 01800021 002169 B6 04010000 [....!....i!.....]
90 C 8410 0000289 A 00000000 00000000 00000000 [.(..............]
90 C 8420 00000000 00000001 00000007 00001020 [............ ...]
90 C 8430 00000000 00000000 00000007 01800022 [............"...]
90C8440 00000000 00000000 00000000 00000000 [................]
90C8450 00000000 00000000 00000000 00000001 [................]
90C8460 00000000 0000CF2C 40000000 01800022 [....,......@"...]
90 C 8470 00000007 00000000 00000000 00000000 [................]
90 C 8480 00000000 00000000 00000000 00000000 [................]
Repeat 250 times
90 C 9430 00000000 00010000 00010001 00000000 [................]
90 C 9440 00000000 00000000 00000000 00000000 [................]
Repeat 250 times
90 CA3F0 00000000 00000000 00000000 69 B61001 [...............i]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7
last map 0 x00000000 #maps: 0 offset: 4128
Highwater:: 0x01800022 ext#: 0 blk#: 0 ext size: 7
#blocks in seg. hdr 's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 53036 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x01800022 length: 7
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
rdba:
0
x01800021
表示此时的
datablock address
为
0
x01800021
Highwater:: 0x01800022
表示此时的
HWM
地址为
0x01800022
向表里插入
3
条数据:
declare
m_name varchar2( 2000 ):= '' ;
begin
for i in 1 .. 10 loop
m_name:=m_name|| 'a' ;
end loop;
for j in 1 .. 3 loop
insert into MILIATEST(ID,name) values(j,m_name);
commit;
end loop;
end;
m_name varchar2( 2000 ):= '' ;
begin
for i in 1 .. 10 loop
m_name:=m_name|| 'a' ;
end loop;
for j in 1 .. 3 loop
insert into MILIATEST(ID,name) values(j,m_name);
commit;
end loop;
end;
SQL> exec show_space('MILIATEST');
Free Blocks.............................1
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................6
Last Used Ext BlockId...................33
Last Used Block.........................2
此时段
MILIATEST
用了
2
块
block
,
33
放了段
head
信息,
34
放数据信息。
SQL> alter system dump datafile 6 block min 33 block max 34;
System altered
Start dump data blocks tsn: 7 file#: 6 minblk 33 maxblk 34
buffer tsn: 7 rdba: 0x01800021 (6/33)
scn: 0x0000.00216f8b seq: 0x01 flg: 0x04 tail: 0x6f8b1001
frmt: 0x02 chkval: 0x289a type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x090C8400 to 0x090CA400
90C
8400 0000A210 01800021 00216F8B 04010000 [....!....o!.....]
90C
8410 0000289A 00000000 00000000 00000000 [.(..............]
90C
8420 00000000 00000001 00000007 00001020 [............ ...]
90C
8430 00000000 00000001 00000007 01800023 [............#...]
90C
8440 00000000 00000000 00000001 00000001 [................]
90C
8450 00000000 00000000 00000000 00000001 [................]
90C
8460 00000000 0000CF2C 40000000 01800022 [....,......@"...]
90C
8470 00000007 00000000 00000000 00000000 [................]
90C
8480 00000000 00000000 00000000 00000000 [................]
Repeat 250 times
90C
9430 00000000 00010000 00010001 00000001 [................]
90C
9440 00000000 00000001 01800022 01800022 [........"..."...]
90C
9450 00000000 00000000 00000000 00000000 [................]
Repeat 249 times
90CA3F0 00000000 00000000 00000000 6F8B1001 [...............o]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x01800023
ext#: 0 blk#: 1 ext size: 7 --
此时
HWM
为:
0x01800023
#blocks in seg. hdr's freelists: 1
#blocks below: 1
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 53036 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x01800022 length: 7
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 1
SEG LST:: flg: USED lhd: 0x01800022 ltl: 0x01800022
buffer tsn: 7 rdba: 0x01800022 (6/34)
scn: 0x0000.00217094 seq: 0x02 flg: 0x06 tail: 0x70940602
frmt: 0x02 chkval: 0x22ca type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x090C8400 to 0x090CA400
90C
8400 0000A206 01800022 00217094 06020000 [...."....p!.....]
90C
8410 000022CA 00000001 0000CF2C 00217093 [."......,....p!.]
90C
8420 00000000 00030002 00000000 000B0002 [................]
90C
8430 00000200 00800345 00190179 00002001 [....E...y.... ..]
90C
8440 00217094 002A0003 0000020D 008003DB [.p!...*.........]
90C
8450 00260161 00008000 00217045 00030100 [a.&.....Ep!.....]
90C
8460 0018FFFF 1F551F6D 00001F55 1F8F0003 [....m.U.U.......]
90C
8470 1F6D1F7E 00000000 00000000 00000000 [~.m.............]
90C
8480 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
90CA3C0 00000000 00000000 02012C00 0A04C102 [.........,......]
90CA3D0 61616161 61616161 002C6161 03C10202 [aaaaaaaaaa,.....]
90CA3E0 6161610A 61616161 2C616161 C1020200 [.aaaaaaaaaa,....]
90CA3F0 61610A02 61616161 61616161 70940602 [..aaaaaaaaaa...p]
Block header dump: 0x01800022
Object id on Block? Y
seg/obj: 0xcf2c csc: 0x00.217093 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.00b.00000200 0x00800345.0179.19 --U- 1 fsc 0x0000.00217094
0x02 0x0003.02a.0000020d 0x008003db.0161.26 C--- 0 scn 0x0000.00217045
data_block_dump,data header at 0x90c845c
===============
tsiz: 0x1fa0
--
Total data area size
:
8k
的
block: 8192-20(block head)-24(Transaction Header)-24*2(
一个事务条
)-4(block tail)=8096(0x1fa0)
hsiz: 0x18 --data head size
pbl: 0x090c845c
bdba: 0x01800022
76543210
flag=--------
ntab=1 --
表示存放一张表的数据,当存放
cluster
时,可能出现
ntab
〉
1
nrow=3 --
表示现在该
block
内有三行数据
frre=-1
fsbo=0x18 --
表示可以放数据的空间的起始位置(发现该值和
hsiz
保持一至)
fseo=0x1f6d --
表示可以存放数据的
end
位置
: 8062
avsp=0x1f55
tosp=0x1f55
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f8f --0x1f8f+tl:17=8079+17=8096
0x14:pri[1] offs=0x1f7e --0x1f7e+t1:17=8062+17=8079
0x16:pri[2] offs=0x1f6d --0x1f6d+t1:17=8045+17=8062
block_row_dump:
tab 0, row 0, @0x1f8f
tl: 17
fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [10] 61 61 61 61 61 61 61 61 61 61
tab 0, row 1, @0x1f7e
tl: 17
fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [10] 61 61 61 61 61 61 61 61 61 61
tab 0, row 2, @0x1f6d
tl: 17
fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [10] 61 61 61 61 61 61 61 61 61 61
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 33 maxblk 34
分析上面的灰色部分,翻译如下:
90CA3C0 00000000 00000000 02012C00 0A04C102 [.........,......]
翻译:
002C0102 02C1040A
. . . . . . . . .
, . . . . . .
90CA3D0 61616161 61616161 002C6161 03C10202 [aaaaaaaaaa,.....]
61616161 61616161 61612C00 0202C103
a a a a a a a a a a
, . . . . .
90CA3E0 6161610A 61616161 2C616161 C1020200 [.aaaaaaaaaa,....]
0A616161 61616161 6161612C 000202C1
. a a a a a a a a a a
, . . . .
90CA3F0 61610A02 61616161 61616161 70940602 [..aaaaaaaaaa...p]
020A6161 61616161 61616161
02069470 –
最后四位为
block tail
. . a a a a a a a a a a
. . . P
0x12:pri[
0
] offs=0x1f8f
对应得物理地址为
90CA3EB-90CA3FB
共
17
个字节
0x14:pri[
1
] offs=0x1f7e
对应得物理地址为
90CA3DA-90CA3EA
共
17
个字节
0x16:pri[
2
] offs=0x1f6d
对应得物理地址为
90CA3C9-90CA3D9
共
17
个字节
0,1,2
对应
rowid
的行号,如下:
SQL
> select a.id,a.name,dbms_rowid.rowid_block_number(rowid) blockid,dbms_rowid.rowid_row_number(rowid) row# from miliatest a;
ID NAME BLOCKID ROW#
----------- --------------- ---------- ----------
1 aaaaaaaaaa 34 0
2 aaaaaaaaaa 34 1
3 aaaaaaaaaa 34 2
ID NAME BLOCKID ROW#
----------- --------------- ---------- ----------
1 aaaaaaaaaa 34 0
2 aaaaaaaaaa 34 1
3 aaaaaaaaaa 34 2
注:
ROWID
组成
OOOOOOFFFBBBBBBRRR
其中,
O
是对象
ID
,
F
是文件
ID
,
B
是块
ID
,
R
是行
ID
通过分析导出的文件,我们看到
rowid
里存放了偏移量数组下标,即所说的行ID,
而没有直接在
rowid
里存放偏移量,那
oracle
为何要如此设计呢,猜想,一个重要原因
是可以在做更新操作的时候(非分区表),而不需要修改索引(更新列不为索引列)
在做更新操作的时候,偏移量会改变吗?
SQL> select rowid
2 from miliatest a
3 where id=2;
ROWID
------------------
AAAM8sAAGAAAAAiAAB
更新第二条记录:
declare
m_address varchar2( 2000 ):= '' ;
begin
for i in 1 .. 2000 loop
m_address:=m_address|| 'b' ;
end loop;
update MILIATEST
set ADRESS=m_address
where id= 2 ;
commit;
end;
m_address varchar2( 2000 ):= '' ;
begin
for i in 1 .. 2000 loop
m_address:=m_address|| 'b' ;
end loop;
update MILIATEST
set ADRESS=m_address
where id= 2 ;
commit;
end;
SQL> select dbms_rowid.rowid_block_number(rowid) blockid,dbms_rowid.rowid_row_number(rowid) row#
2 from miliatest a
3 where id=2;
BLOCKID ROW#
---------- ----------
34
1
SQL> select rowid
2 from miliatest a
3 where id=2;
ROWID
------------------
AAAM8sAAGAAAAAiAAB
可以看出更新后
rowid
没有变化。
alter system dump datafile 6 block 34;
Dump
of memory from
0
x090C8400 to
0
x090CA400
90 C 8400 0000 A 206 01800022 00217 CB3 02020000 [...."....|!.....]
90C8410 00000000 00000001 0000CF2C 00217093 [........,....p!.]
90C8420 00000000 00030002 00000000 000B0002 [................]
90C8430 00000200 00800345 00190179 00002001 [....E...y.... ..]
90C8440 00217094 002F000A 000001C5 0080014B [.p!.../.....K...]
90C8450 0016016D 00002001 00217CB3 00030100 [m.... ...|!.....]
90C8460 0018FFFF 17821789 00001782 1F8F0003 [................]
90C8470 1F6D1789 00000000 00000000 00000000 [..m.............]
90C8480 00000000 00000000 00000000 00000000 [................]
Repeat 373 times
90C9BE0 00000000 03022C00 0A03C102 61616161 [.....,......aaaa]
90C9BF0 61616161 D0FE6161 62626207 62626262 [aaaaaa...bbbbbbb]
90C9C00 62626262 62626262 62626262 62626262 [bbbbbbbbbbbbbbbb]
Repeat 123 times
90CA3C0 62626262 62626262 02012C62 0A04C102 [bbbbbbbbb,......]- 更新后第 1 行
90CA3D0 61616161 61616161 002C6161 03C10202 [aaaaaaaaaa,.....]- 第 2 行
90CA3E0 6161610A 61616161 2C616161 C1020200 [.aaaaaaaaaa ,....]- 更新前第 1 行(这里的数据已经无效了,因为第 1 行偏移量地址已经改变)
90CA3F0 61610A02 61616161 61616161 7CB30602 [..aaaaaaaaaa...|] — 第 0 行
Block header dump: 0x01800022
Object id on Block? Y
seg/obj: 0xcf2c csc: 0x00.217093 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.00b.00000200 0x00800345.0179.19 --U- 1 fsc 0x0000.00217094
0x02 0x000a.02f.000001c5 0x0080014b.016d.16 --U- 1 fsc 0x0000.00217cb3
data_block_dump,data header at 0x90c845c
===============
tsiz: 0x1fa0
hsiz: 0x18
pbl: 0x090c845c
bdba: 0x01800022
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1789
avsp=0x1782
tosp=0x1782
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f8f
0x14:pri[1] offs=0x1789 – 更新后第 1 行偏移量地址改变了,而第 0 行和第 2 行没有影响
90 C 8400 0000 A 206 01800022 00217 CB3 02020000 [...."....|!.....]
90C8410 00000000 00000001 0000CF2C 00217093 [........,....p!.]
90C8420 00000000 00030002 00000000 000B0002 [................]
90C8430 00000200 00800345 00190179 00002001 [....E...y.... ..]
90C8440 00217094 002F000A 000001C5 0080014B [.p!.../.....K...]
90C8450 0016016D 00002001 00217CB3 00030100 [m.... ...|!.....]
90C8460 0018FFFF 17821789 00001782 1F8F0003 [................]
90C8470 1F6D1789 00000000 00000000 00000000 [..m.............]
90C8480 00000000 00000000 00000000 00000000 [................]
Repeat 373 times
90C9BE0 00000000 03022C00 0A03C102 61616161 [.....,......aaaa]
90C9BF0 61616161 D0FE6161 62626207 62626262 [aaaaaa...bbbbbbb]
90C9C00 62626262 62626262 62626262 62626262 [bbbbbbbbbbbbbbbb]
Repeat 123 times
90CA3C0 62626262 62626262 02012C62 0A04C102 [bbbbbbbbb,......]- 更新后第 1 行
90CA3D0 61616161 61616161 002C6161 03C10202 [aaaaaaaaaa,.....]- 第 2 行
90CA3E0 6161610A 61616161 2C616161 C1020200 [.aaaaaaaaaa ,....]- 更新前第 1 行(这里的数据已经无效了,因为第 1 行偏移量地址已经改变)
90CA3F0 61610A02 61616161 61616161 7CB30602 [..aaaaaaaaaa...|] — 第 0 行
Block header dump: 0x01800022
Object id on Block? Y
seg/obj: 0xcf2c csc: 0x00.217093 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.00b.00000200 0x00800345.0179.19 --U- 1 fsc 0x0000.00217094
0x02 0x000a.02f.000001c5 0x0080014b.016d.16 --U- 1 fsc 0x0000.00217cb3
data_block_dump,data header at 0x90c845c
===============
tsiz: 0x1fa0
hsiz: 0x18
pbl: 0x090c845c
bdba: 0x01800022
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1789
avsp=0x1782
tosp=0x1782
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f8f
0x14:pri[1] offs=0x1789 – 更新后第 1 行偏移量地址改变了,而第 0 行和第 2 行没有影响
-0x1789+ tl: 2020=8062
,此时
fseo=0x1789
0x16:pri[2] offs=0x1f6d
block_row_dump:
tab 0, row 0, @0x1f8f
tl: 17 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [10] 61 61 61 61 61 61 61 61 61 61
tab 0, row 1, @0x1789
tl: 2020 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 03
col 1: [10] 61 61 61 61 61 61 61 61 61 61
col 2: [2000]
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
62 62 62 62 62 62........................................................
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
tab 0, row 2, @0x1f6d
tl: 17 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [10] 61 61 61 61 61 61 61 61 61 61
end_of_block_dump
0x16:pri[2] offs=0x1f6d
block_row_dump:
tab 0, row 0, @0x1f8f
tl: 17 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [10] 61 61 61 61 61 61 61 61 61 61
tab 0, row 1, @0x1789
tl: 2020 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 03
col 1: [10] 61 61 61 61 61 61 61 61 61 61
col 2: [2000]
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
62 62 62 62 62 62........................................................
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
tab 0, row 2, @0x1f6d
tl: 17 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [10] 61 61 61 61 61 61 61 61 61 61
end_of_block_dump
设计修改数据导致产生
行迁移:
declare
m_address varchar2( 2000 ):= '' ;
m_name varchar2( 2000 ):= '' ;
begin
for i in 1 .. 2000 loop
m_address:=m_address|| 'b' ;
end loop;
for i in 1 .. 1000 loop
m_name:=m_name|| 'c' ;
end loop;
for j in 1 .. 3 loop
update MILIATEST
set ADRESS=m_address
,name=m_name
where id=j;
commit;
end loop;
end;
m_address varchar2( 2000 ):= '' ;
m_name varchar2( 2000 ):= '' ;
begin
for i in 1 .. 2000 loop
m_address:=m_address|| 'b' ;
end loop;
for i in 1 .. 1000 loop
m_name:=m_name|| 'c' ;
end loop;
for j in 1 .. 3 loop
update MILIATEST
set ADRESS=m_address
,name=m_name
where id=j;
commit;
end loop;
end;
SQL> exec show_space('MILIATEST');
Free Blocks.............................1
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................6
Last Used Ext BlockId...................33
Last Used Block.........................3
Start
dump data blocks tsn:
7
file#:
6
minblk
34
maxblk
35
buffer tsn: 7 rdba: 0 x01800022 ( 6 / 34 )
scn: 0 x0000. 0021803 d seq: 0 x04 flg: 0 x02 tail: 0 x803d0604
frmt: 0 x02 chkval: 0 x0000 type: 0 x06=trans data
Hex dump of block: st= 0 , typ_found= 1
tsiz: 0 x1fa0
hsiz: 0 x18
pbl: 0 x090c845c
bdba: 0 x01800022
76543210
flag= --------
ntab= 1
nrow= 3
frre=- 1
fsbo= 0 x18
fseo= 0 x80f
avsp= 0 x7f7
tosp= 0 x7f7
0 xe:pti[ 0 ] nrow= 3 offs= 0
0 x12:pri[ 0 ] offs= 0 x13dc =5084 +3012byte=8096
0 x14:pri[ 1 ] offs= 0 x13d3 =5075 +9byte=5084
0 x16:pri[ 2 ] offs= 0 x80f +3012byte=5075
block_row_dump:
tab 0 , row 0 , @ 0 x13dc
tl: 3012 fb: --H-FL-- lb: 0x0 cc: 3
col 0 : [ 2 ] c1 02
col 1 : [ 1000 ]
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
..........................................................................
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
col 2 : [ 2000 ]
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
.........................................................................
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
tab 0, row 1, @0x13d3
tl: 9 fb: --H----- lb: 0x0 cc: 0 列的个数为 0 ,表示没有存放数据
nrid: 0x01800023.0 表示迁移的地址为 : rdba 为 0x01800023 ,行号为 0 的地方
tab 0, row 2, @0x80f
tl: 3012 fb: --H-FL-- lb: 0x2 cc: 3
col 0 : [ 2 ] c1 04
col 1 : [ 1000 ]
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
.........................................................................
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
col 2 : [ 2000 ]
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
.........................................................................
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
end_of_block_dump
buffer tsn: 7 rdba: 0 x01800022 ( 6 / 34 )
scn: 0 x0000. 0021803 d seq: 0 x04 flg: 0 x02 tail: 0 x803d0604
frmt: 0 x02 chkval: 0 x0000 type: 0 x06=trans data
Hex dump of block: st= 0 , typ_found= 1
tsiz: 0 x1fa0
hsiz: 0 x18
pbl: 0 x090c845c
bdba: 0 x01800022
76543210
flag= --------
ntab= 1
nrow= 3
frre=- 1
fsbo= 0 x18
fseo= 0 x80f
avsp= 0 x7f7
tosp= 0 x7f7
0 xe:pti[ 0 ] nrow= 3 offs= 0
0 x12:pri[ 0 ] offs= 0 x13dc =5084 +3012byte=8096
0 x14:pri[ 1 ] offs= 0 x13d3 =5075 +9byte=5084
0 x16:pri[ 2 ] offs= 0 x80f +3012byte=5075
block_row_dump:
tab 0 , row 0 , @ 0 x13dc
tl: 3012 fb: --H-FL-- lb: 0x0 cc: 3
col 0 : [ 2 ] c1 02
col 1 : [ 1000 ]
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
..........................................................................
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
col 2 : [ 2000 ]
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
.........................................................................
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
tab 0, row 1, @0x13d3
tl: 9 fb: --H----- lb: 0x0 cc: 0 列的个数为 0 ,表示没有存放数据
nrid: 0x01800023.0 表示迁移的地址为 : rdba 为 0x01800023 ,行号为 0 的地方
tab 0, row 2, @0x80f
tl: 3012 fb: --H-FL-- lb: 0x2 cc: 3
col 0 : [ 2 ] c1 04
col 1 : [ 1000 ]
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
.........................................................................
63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63 63
col 2 : [ 2000 ]
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
.........................................................................
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
end_of_block_dump
我们再看看
block35
的
dump
文件:
buffer tsn:
7
rdba:
0
x01800023 (
6
/
35
)
scn: 0 x0000. 0021803 b seq: 0 x05 flg: 0 x02 tail: 0 x803b0605
frmt: 0 x02 chkval: 0 x0000 type: 0 x06=trans data
Hex dump of block: st= 0 , typ_found= 1
Dump of memory from 0 x090C8400 to 0 x090CA400
Block header dump: 0 x01800023
Object id on Block? Y
seg/obj: 0 xcf2c csc: 0 x00. 218039 itc: 3 flg: O typ: 1 - DATA
fsl: 0 fnx: 0 x0 ver: 0 x01
Itl Xid Uba Flag Lck Scn/Fsc
0 x01 0 x0009. 00 b. 00000216 0 x00800058. 0198.1 c --U- 1 fsc 0x0000.0021803b
0 x02 0 x0000. 000.00000000 0 x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0 x03 0 x0000. 000.00000000 0 x00000000.0000.00 C --- 0 scn 0x0000.00000000
data_block_dump,data header at 0 x90c8474
===============
tsiz: 0 x1f88
hsiz: 0 x14
pbl: 0 x090c8474
bdba: 0 x01800023
76543210
flag= --------
ntab= 1
nrow= 1
frre=- 1
fsbo= 0 x14
fseo= 0 x13be
avsp= 0 x13aa
tosp= 0 x13aa
0 xe:pti[ 0 ] nrow= 1 offs= 0
0 x12:pri[ 0 ] offs= 0 x13be
block_row_dump:
tab 0 , row 0 , @ 0 x13be
tl: 3018 fb: ----FL-- lb: 0x1 cc: 3
hrid: 0x01800022.1 --表示这行是从 rdba 为 0x01800022 ,行号为 1 的地方迁移来的
col 0 : [ 2 ] c1 03
col 1 : [ 1000 ]
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
.........................................................................
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
col 2 : [ 2000 ]
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
.........................................................................
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
end_of_block_dump
scn: 0 x0000. 0021803 b seq: 0 x05 flg: 0 x02 tail: 0 x803b0605
frmt: 0 x02 chkval: 0 x0000 type: 0 x06=trans data
Hex dump of block: st= 0 , typ_found= 1
Dump of memory from 0 x090C8400 to 0 x090CA400
Block header dump: 0 x01800023
Object id on Block? Y
seg/obj: 0 xcf2c csc: 0 x00. 218039 itc: 3 flg: O typ: 1 - DATA
fsl: 0 fnx: 0 x0 ver: 0 x01
Itl Xid Uba Flag Lck Scn/Fsc
0 x01 0 x0009. 00 b. 00000216 0 x00800058. 0198.1 c --U- 1 fsc 0x0000.0021803b
0 x02 0 x0000. 000.00000000 0 x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0 x03 0 x0000. 000.00000000 0 x00000000.0000.00 C --- 0 scn 0x0000.00000000
data_block_dump,data header at 0 x90c8474
===============
tsiz: 0 x1f88
hsiz: 0 x14
pbl: 0 x090c8474
bdba: 0 x01800023
76543210
flag= --------
ntab= 1
nrow= 1
frre=- 1
fsbo= 0 x14
fseo= 0 x13be
avsp= 0 x13aa
tosp= 0 x13aa
0 xe:pti[ 0 ] nrow= 1 offs= 0
0 x12:pri[ 0 ] offs= 0 x13be
block_row_dump:
tab 0 , row 0 , @ 0 x13be
tl: 3018 fb: ----FL-- lb: 0x1 cc: 3
hrid: 0x01800022.1 --表示这行是从 rdba 为 0x01800022 ,行号为 1 的地方迁移来的
col 0 : [ 2 ] c1 03
col 1 : [ 1000 ]
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
.........................................................................
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
col 2 : [ 2000 ]
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
.........................................................................
62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62
end_of_block_dump
为什么说上面发生的是行迁移而不是行链接呢?
要解答这个问题,就要明白行链接和行迁移的区别了,发生行迁移后,在
dump
文件里该行的
cc: 0
列的个数为
0
而发生行链接,
cc
的个数是不为零的,在原来的地址仍然存放着数据的。
从上面地实验可以看出
oracle
是这样存储和访问数据的:
结论:
1、当我们往非分区表里插入一条数据后:在block内记录下它的行id(安先后插入顺序,从0开始),以及它在block内的偏移量地址(offs=XXXX)
oracle数据是从block底向上存放的,第一次Insert时offs从大到小
1、当我们往非分区表里插入一条数据后:在block内记录下它的行id(安先后插入顺序,从0开始),以及它在block内的偏移量地址(offs=XXXX)
oracle数据是从block底向上存放的,第一次Insert时offs从大到小
2、当我们修改数据时,行id是不变的,也就是它的rowid不变,而只是修改偏移量数组中对应的偏移量
Oracle在通过rowid读取数据时,先定位block,在通过行id,得到offs,最后得到具体值
当全表扫描时,在同一个block内数据顺序是偏移量数组的顺序,不是实际存放数据的顺序,发生行迁移后遵循block顺序
Oracle在通过rowid读取数据时,先定位block,在通过行id,得到offs,最后得到具体值
当全表扫描时,在同一个block内数据顺序是偏移量数组的顺序,不是实际存放数据的顺序,发生行迁移后遵循block顺序
3、行迁移时,它的rowid也是不变的,只是在原来存放数据的地方放一个nrid,指示数据被存放的block地址及行号。