行链接模拟及dml操作对行链接的影响

1.创建测试表,模拟行链接：

SQL> create table test(a char(2000),b char(2000),c char(2000),d char(2000));

Table created.

SQL> insert into test values('a','b','c','d');

1 row created.

SQL> commit;

Commit complete.

2.获得该行所在file#及block#，并进行dump

SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid)
  2  from test;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   4                                17326

SQL> alter system dump datafile 4 block 17326;

System altered.

此时查看生成的dump文件，

Block header dump:  0x010043ae
 Object id on Block? Y
 seg/obj: 0x3320  csc: 0x00.2da4ed  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10043a9 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.02c.000002db  0x00800909.03a9.35  --U-    1  fsc 0x0000.002da4f7
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 
data_block_dump,data header at 0xceb5864
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0ceb5864
bdba: 0x010043ae
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x17bc
avsp=0x17a8
tosp=0x17a8
0xe:pti[0]      nrow=1  ffs=0
0x12:pri[0]     ffs=0x17bc
block_row_dump:
tab 0, row 0, @0x17bc
tl: 2012 fb: --H-F--- lb: 0x1  cc: 1
nrid:  0x010043ad.0
col  0: [2000]
 61 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

注意nrid:  0x010043ad.0指向了该行的另一个row piece的地址，010043ad转换为10进制后为16794541，再用dbms_utility工具获得block位置：

SQL> select dbms_utility.data_block_address_file(16794541) "file",
  2  dbms_utility.data_block_address_block(16794541) "block" 
  3  from dual;

      file      block
---------- ----------
         4      17325

SQL> alter system dump datafile 4 block 17325;

System altered.

查看该block的dump文件：

 seg/obj: 0x3320  csc: 0x00.2da4ed  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10043a9 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.02c.000002db  0x00800909.03a9.34  --U-    1  fsc 0x0000.002da4f7
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
 
data_block_dump,data header at 0xceb587c
===============
tsiz: 0x1f80
hsiz: 0x14
pbl: 0x0ceb587c
bdba: 0x010043ad
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x804
avsp=0x7f0
tosp=0x7f0
0xe:pti[0]      nrow=1  ffs=0
0x12:pri[0]     ffs=0x804
block_row_dump:
tab 0, row 0, @0x804
tl: 6012 fb: -----L-- lb: 0x1  cc: 3
col  0: [2000]
 62 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

3. 再看当执行dml操作时，所加行级锁的情况：

SQL> update test set a='b';

1 row updated.

SQL> alter system dump datafile 4 block 17326;

System altered.

SQL> alter system dump datafile 4 block 17325;

System altered.

查看生成的dump文件：

Block header dump:  0x010043ae
 Object id on Block? Y
 seg/obj: 0x3320  csc: 0x00.2da722  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10043a9 ver: 0x01 opc: 0
     inc: 0  exflg: 0
    
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.02c.000002db  0x00800909.03a9.35  C---    0  scn 0x0000.002da4f7
0x02   0x0002.007.0000029a  0x0080079c.038c.1e  ----    1  fsc 0x0000.00000000

。。。。。。。。。。。。。

Block header dump:  0x010043ad
 Object id on Block? Y
 seg/obj: 0x3320  csc: 0x00.2da4ed  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10043a9 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.02c.000002db  0x00800909.03a9.34  --U-    1  fsc 0x0000.002da4f7
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.00000000
 

可见对于行链接，如果修改的数据不位于链接的block上，那么在链接的block上是不会加锁的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-612884/，如需转载，请注明出处，否则将追究法律责任。