oracle表 inittrans,MAXTRANS和inittrans 有几个人能说清楚

weixin_39559804

于 2021-04-12 06:29:46 发布

阅读量419

点赞数

文章标签： oracle表 inittrans

看了世界杯第一场,想睡睡不着,起来想看第二场,又被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

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要这样做呢，长度比原来都短,为何不在原来位置进行更新?

继续实验,继续插入数据直到块满,最后这部分空间也没被利用,疑惑......

weixin_39559804

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle表 inittrans,MAXTRANS和inittrans 有几个人能说清楚

看了世界杯第一场,想睡睡不着,起来想看第二场,又被BITI大师的问题给难住了，没心思看了,自己做测试了SQL> create table testitl ( name varchar2(10) ) ;Table created.SQL> insert into testitl values ('sun') ;1 row created.SQL> c/sun/rong/1* ins...
复制链接

扫一扫