使用dbms_metadata.get_ddl得到表的ddl语句，增加itl事务槽个数_dbms_metadata 生成ddl

使用第三方工具PLSQL Developer可以很快速地获得表的ddl语句，以及表的索引等详细信息。

但是我们同时也可以使用dbms_metadata.get_ddl得到表的ddl：

select dbms_metadata.get_ddl(object_type => 'TABLE',
                             name        => 'T1',
                             schema      => 'SCOTT')
  from dual

返回CLOB，查看之：

  CREATE TABLE "SCOTT"."T1" 
   ( "DEPTNO" NUMBER(2,0), 
"DNAME" VARCHAR2(14), 
"LOC" VARCHAR2(13)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
 范围的表创建语句较为详细，其中有两个参数：

 INITRANS 1 MAXTRANS 255是值得研究的，它表示了段内数据块的初识事务槽个数和最大事务槽个数（事务槽可以自动根据事务量而增加）。

下面来看看我们的说法是否正确：

SQL> select rowid,a.*,
  2  dbms_rowid.rowid_object(rowid)  object_id,  ----段对象的id
  3  dbms_rowid.rowid_relative_fno(rowid) file_id, ---文件编号
  4  dbms_rowid.rowid_block_number(rowid)  block_id, ---块号
  5  dbms_rowid.rowid_row_number(rowid) row_num ---行号
  6  from t1 a where rownum<=10;


ROWID                  DEPTNO DNAME          LOC            OBJECT_ID    FILE_ID   BLOCK_ID    ROW_NUM
------------------ ---------- -------------- ------------- ---------- ---------- ---------- ----------
AAASNnAAEAAAAMrAAA         10 ACCOUNTING     NEW YORK           74599          4        811          0
AAASNnAAEAAAAMrAAB         20 RESEARCH       DALLAS             74599          4        811          1
AAASNnAAEAAAAMrAAC         30 SALES          CHICAGO            74599          4        811          2
AAASNnAAEAAAAMrAAD         40 OPERATIONS     BOSTON             74599          4        811          3
AAASNnAAEAAAAMvAAA         10 ACCOUNTING     NEW YORK           74599          4        815          0
AAASNnAAEAAAAMvAAB         20 RESEARCH       DALLAS             74599          4        815          1
AAASNnAAEAAAAMvAAC         30 SALES          CHICAGO            74599          4        815          2
AAASNnAAEAAAAMvAAD         40 OPERATIONS     BOSTON             74599          4        815          3
AAASNnAAEAAAAMvAAE         10 ACCOUNTING     NEW YORK           74599          4        815          4
AAASNnAAEAAAAMvAAF         20 RESEARCH       DALLAS             74599          4        815          5


已选择10行。


SQL>

我们准备使用file 4 block 811来试验。

根据上面的查询得出，在file 4 block811上有4条记录，来看看当前的itl个数：

SQL> alter system dump datafile 4 block 811;


系统已更改。


SQL>

转储文件：

*** 2015-10-09 14:20:25.988
*** SESSION ID:(67.21) 2015-10-09 14:20:25.988
*** CLIENT ID:() 2015-10-09 14:20:25.988
*** SERVICE NAME:(SYS$USERS) 2015-10-09 14:20:25.988
*** MODULE NAME:(SQL*Plus) 2015-10-09 14:20:25.988
*** ACTION NAME:() 2015-10-09 14:20:25.988
 
Start dump data blocks tsn: 4 file#:4 minblk 811 maxblk 811
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16778027
BH (0x2A7D4BC4) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2A1EE000
  set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2C7DD0C0,0x3D7951CC] lru: [0x2B7E4FA4,0x2AFF8F4C]
  ckptq: [NULL] fileq: [NULL] objq: [0x391022E8,0x2AFF8F64]
  st: XCURRENT md: NULL tch: 3
  flags: only_sequential_access
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2C7DD044) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2C32E000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2BFC9F2C,0x2A7D4C40] lru: [0x2AFD2CF8,0x2C7DD864]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2BFC9EB0) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B84C000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2BFCA424,0x2C7DD0C0] lru: [0x30FD346C,0x2BFC9E88]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2BFCA3A8) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B858000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x3D7951CC,0x2BFC9F2C] lru: [0x2C7F2478,0x2FFE4ED0]
  lru-flags: moved_to_tail on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x0100032b (4/811)
scn: 0x0000.0015b3f8 seq: 0x01 flg: 0x04 tail: 0xb3f80601
frmt: 0x02 chkval: 0x1d4f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0E622200 to 0x0E624200
E622200 0000A206 0100032B 0015B3F8 04010000  [....+...........]
E622210 00001D4F 00000001 00012367 0010BEF3  [O.......g#......]
E622220 00000000 00320003 01000328 0000FFFF  [......2.(.......]
E622230 00000000 00000000 00000000 00008000  [................]
E622240 0010BEF3 00000000 00000000 00000000  [................]
E622250 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
E622270 00000000 00000000 00000000 00040100  [................]
E622280 001AFFFF 1F0A1EC8 00001F0A 1EC80004  [................]
E622290 1F3C1F50 00001F24 00000000 00000000  [P.<.$...........]
E6222A0 00000000 00000000 00000000 00000000  [................]
        Repeat 489 times
E624140 00000000 0203002C 410A0BC1 554F4343  [....,......ACCOU]
E624150 4E49544E 454E0847 4F592057 022C4B52  [NTING.NEW YORK,.]
E624160 0BC10203 41414104 454E0841 4F592057  [.....AAAA.NEW YO]
E624170 002C4B52 0BC10203 4343410A 544E554F  [RK,......ACCOUNT]
E624180 08474E49 2057454E 4B524F59 0203022C  [ING.NEW YORK,...]
E624190 41040BC1 08414141 2057454E 4B524F59  [...AAAA.NEW YORK]
E6241A0 0203002C 4F0A29C1 41524550 4E4F4954  [,....).OPERATION]
E6241B0 4F420653 4E4F5453 0203002C 53051FC1  [S.BOSTON,......S]
E6241C0 53454C41 49484307 4F474143 0203002C  [ALES.CHICAGO,...]
E6241D0 520815C1 41455345 06484352 4C4C4144  [...RESEARCH.DALL]
E6241E0 002C5341 0BC10203 4343410A 544E554F  [AS,......ACCOUNT]
E6241F0 08474E49 2057454E 4B524F59 B3F80601  [ING.NEW YORK....]
Block header dump:  0x0100032b
 Object id on Block? Y
 seg/obj: 0x12367  csc: 0x00.10bef3  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000328 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.0010bef3
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0100032b
data_block_dump,data header at 0xe62227c
===============
tsiz: 0x1f80
hsiz: 0x1a
pbl: 0x0e62227c
     76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1ec8
avsp=0x1f0a
tosp=0x1f0a
0xe:pti[0] nrow=4offs=0
0x12:pri[0] offs=0x1ec8
0x14:pri[1] offs=0x1f50
0x16:pri[2] offs=0x1f3c
0x18:pri[3] offs=0x1f24
block_row_dump:
tab 0, row 0, @0x1ec8
tl: 26 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 0b
col  1: [10]  41 43 43 4f 55 4e 54 49 4e 47
col  2: [ 8]  4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1f50
tl: 22 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 15
col  1: [ 8]  52 45 53 45 41 52 43 48
col  2: [ 6]  44 41 4c 4c 41 53
tab 0, row 2, @0x1f3c
tl: 20 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 1f
col  1: [ 5]  53 41 4c 45 53
col  2: [ 7]  43 48 49 43 41 47 4f
tab 0, row 3, @0x1f24
tl: 24 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 29
col  1: [10]  4f 50 45 52 41 54 49 4f 4e 53
col  2: [ 6]  42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 811 maxblk 811

可以看到上面有3个itl事务槽，并且没有事务发生。

由于itl事务槽可以重复使用（但是被使用的时候，是独占使用的），所以我们在这个数据块上启动3个事务时，itl事务槽的个数并不会增加：

启动一个事务，不提交：

SQL> update t1 set dname='A1' where rowid='AAASNnAAEAAAAMrAAA';


已更新 1 行。


SQL>

另外session，再启动一个事务，不提交：

SQL> update t1 set dname='A1' where rowid='AAASNnAAEAAAAMrAAB';


已更新 1 行。


SQL>

根据前面的查询结果得知，这两个事务影响的数据块都在file 4 block 811上。我们转储该数据块看看：

*** 2015-10-09 14:30:16.621
*** SESSION ID:(195.49) 2015-10-09 14:30:16.621
*** CLIENT ID:() 2015-10-09 14:30:16.621
*** SERVICE NAME:(SYS$USERS) 2015-10-09 14:30:16.621
*** MODULE NAME:(SQL*Plus) 2015-10-09 14:30:16.621
*** ACTION NAME:() 2015-10-09 14:30:16.621
 
Start dump data blocks tsn: 4 file#:4 minblk 811 maxblk 811
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16778027
BH (0x2A7D4BC4) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2A1EE000
  set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2C7DD0C0,0x3D7951CC] lru: [0x28FDC2DC,0x3C987B7C]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2C7DD044) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2C32E000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2BFC9F2C,0x2A7D4C40] lru: [0x2AFD2CF8,0x2C7DD864]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2BFC9EB0) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B84C000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2BFCA424,0x2C7DD0C0] lru: [0x30FD346C,0x2BFC9E88]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2BFCA3A8) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B858000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x3D7951CC,0x2BFC9F2C] lru: [0x2C7F2478,0x2FFE4ED0]
  lru-flags: moved_to_tail on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x0100032b (4/811)
scn: 0x0000.0015b937 seq: 0x01 flg: 0x04 tail: 0xb9370601
frmt: 0x02 chkval: 0x3a9c type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0F3D2200 to 0x0F3D4200
F3D2200 0000A206 0100032B 0015B937 04010000  [....+...7.......]
F3D2210 00003A9C 00000001 00012367 0010BEF3  [.:......g#......]
F3D2220 00000000 00320003 01000328 0000FFFF  [......2.(.......]
F3D2230 00000000 00000000 00000000 00008000  [................]
F3D2240 0010BEF3 0000000A 000002E0 00C00A9C  [................]
F3D2250 000D0090 00080001 00000000 00140005  [................]
F3D2260 0000048B 00C0049D 002E00B3 00060001  [................]
F3D2270 00000000 00000000 00000000 00040100  [................]
F3D2280 001AFFFF 1F0A1EA6 00001F18 1EB60004  [................]
F3D2290 1F3C1EA6 00001F24 00000000 00000000  [..<.$...........]
F3D22A0 00000000 00000000 00000000 00000000  [................]
        Repeat 487 times
F3D4120 032C0000 15C10203 06324102 4C4C4144  [..,......A2.DALL]
F3D4130 022C5341 0BC10203 08314102 2057454E  [AS,......A1.NEW ]
F3D4140 4B524F59 0203002C 410A0BC1 554F4343  [YORK,......ACCOU]
F3D4150 4E49544E 454E0847 4F592057 022C4B52  [NTING.NEW YORK,.]
F3D4160 0BC10203 41414104 454E0841 4F592057  [.....AAAA.NEW YO]
F3D4170 002C4B52 0BC10203 4343410A 544E554F  [RK,......ACCOUNT]
F3D4180 08474E49 2057454E 4B524F59 0203022C  [ING.NEW YORK,...]
F3D4190 41040BC1 08414141 2057454E 4B524F59  [...AAAA.NEW YORK]
F3D41A0 0203002C 4F0A29C1 41524550 4E4F4954  [,....).OPERATION]
F3D41B0 4F420653 4E4F5453 0203002C 53051FC1  [S.BOSTON,......S]
F3D41C0 53454C41 49484307 4F474143 0203002C  [ALES.CHICAGO,...]
F3D41D0 520815C1 41455345 06484352 4C4C4144  [...RESEARCH.DALL]
F3D41E0 002C5341 0BC10203 4343410A 544E554F  [AS,......ACCOUNT]
F3D41F0 08474E49 2057454E 4B524F59 B9370601  [ING.NEW YORK..7.]
Block header dump:  0x0100032b
 Object id on Block? Y
 seg/obj: 0x12367  csc: 0x00.10bef3  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000328 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.0010bef3
0x02   0x000a.000.000002e0  0x00c00a9c.0090.0d  ----    1  fsc 0x0008.00000000
0x03   0x0005.014.0000048b  0x00c0049d.00b3.2e  ----    1 fsc 0x0006.00000000
bdba: 0x0100032b
data_block_dump,data header at 0xf3d227c
===============
tsiz: 0x1f80
hsiz: 0x1a
pbl: 0x0f3d227c
     76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1ea6
avsp=0x1f0a
tosp=0x1f18
0xe:pti[0] nrow=4offs=0
0x12:pri[0] offs=0x1eb6
0x14:pri[1] offs=0x1ea6
0x16:pri[2] offs=0x1f3c
0x18:pri[3] offs=0x1f24
block_row_dump:
tab 0, row 0, @0x1eb6
tl: 18 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 0b
col  1: [ 2]  41 31
col  2: [ 8]  4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1ea6
tl: 16 fb: --H-FL-- lb: 0x3  cc: 3
col  0: [ 2]  c1 15
col  1: [ 2]  41 32
col  2: [ 6]  44 41 4c 4c 41 53
tab 0, row 2, @0x1f3c
tl: 20 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 1f
col  1: [ 5]  53 41 4c 45 53
col  2: [ 7]  43 48 49 43 41 47 4f
tab 0, row 3, @0x1f24
tl: 24 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 29
col  1: [10]  4f 50 45 52 41 54 49 4f 4e 53
col  2: [ 6]  42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 811 maxblk 811

可以看到itl事务槽有两个事务，而此时itl事务槽个数并没有增加。当然不会，我们才启动两个事务，再启动两个试试：

SQL> update t1 set dname='A3' where rowid='AAASNnAAEAAAAMrAAC';


已更新 1 行。


SQL>

另外session：

SQL> update t1 set dname='A3' where rowid='AAASNnAAEAAAAMrAAD';


已更新 1 行。


SQL>

再来来看看itl的事务槽个数（应该增加到4个了）：

*** 2015-10-09 14:46:07.535
Start dump data blocks tsn: 4 file#:4 minblk 811 maxblk 811
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16778027
BH (0x287D7FF0) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2826C000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x287D8140,0x3D7951CC] lru: [0x287D7BA4,0x3C9876D4]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x287D80C4) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2826E000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0x287E99FC,0x287D806C] lru: [0x287D9550,0x287D7E20]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x287E9980) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x28514000
  set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x28FF0F94,0x287D8140] lru: [0x28FDA1BC,0x287EDAC4]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x28FF0F18) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x28E30000
  set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x28FF1C00,0x287E99FC] lru: [0x28FF0EF0,0x287CAE44]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x28FF1B84) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x28E4E000
  set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2A7D4C40,0x28FF0F94] lru: [0x28FF1314,0x28FF1098]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2A7D4BC4) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2A1EE000
  set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2C7DD0C0,0x28FF1C00] lru: [0x28FDC2DC,0x28FDBB68]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2C7DD044) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2C32E000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2BFC9F2C,0x2A7D4C40] lru: [0x2AFD2CF8,0x2C7DD864]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2BFC9EB0) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B84C000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2BFCA424,0x2C7DD0C0] lru: [0x30FD346C,0x2BFC9E88]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2BFCA3A8) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B858000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x3D7951CC,0x2BFC9F2C] lru: [0x2C7F2478,0x2FFE4ED0]
  lru-flags: moved_to_tail on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x0100032b (4/811)
scn: 0x0000.0015bb36 seq: 0x01 flg: 0x04 tail: 0xbb360601
frmt: 0x02 chkval: 0xd049 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0F3D2200 to 0x0F3D4200
F3D2200 0000A206 0100032B 0015BB36 04010000  [....+...6.......]
F3D2210 0000D049 00000001 00012367 0015BA74  [I.......g#..t...]
F3D2220 00000000 00320004 01000328 00100002  [......2.(.......]
F3D2230 00000396 00C001B3 002A01F5 00030001  [..........*.....]
F3D2240 00000000 0000000A 000002E0 00C00A9C  [................]
F3D2250 000D0090 00080001 00000000 001A0007  [................]
F3D2260 00000307 00C007BC 002B0163 00060001  [........c.+.....]
F3D2270 00000000 00150001 000002EE 00C0008C  [................]
F3D2280 002200FB 00080001 00000000 00000000  [..".............]
F3D2290 00000000 00040100 001AFFFF 1EF21DD5  [................]
F3D22A0 00001F0B 1E9E0004 1DE51E0E 00001DD5  [................]
F3D22B0 00000000 00000000 00000000 00000000  [................]
        Repeat 474 times
F3D4060 00000000 00000000 03042C00 0229C102  [.........,....).]
F3D4070 42063341 4F54534F 03012C4E 021FC102  [A3.BOSTON,......]
F3D4080 43073341 41434948 002C4F47 29C10203  [A3.CHICAGO,....)]
F3D4090 45504F0A 49544152 06534E4F 54534F42  [.OPERATIONS.BOST]
F3D40A0 032C4E4F 15C10203 06324102 4C4C4144  [ON,......A2.DALL]
F3D40B0 002C5341 15C10203 53455208 43524145  [AS,......RESEARC]
F3D40C0 41440648 53414C4C 0203012C 410229C1  [H.DALLAS,....).A]
F3D40D0 4F420633 4E4F5453 0203002C 53051FC1  [3.BOSTON,......S]
F3D40E0 53454C41 49484307 4F474143 0203012C  [ALES.CHICAGO,...]
F3D40F0 41021FC1 48430733 47414349 03002C4F  [...A3.CHICAGO,..]
F3D4100 051FC102 454C4153 48430753 47414349  [....SALES.CHICAG]
F3D4110 03012C4F 021FC102 43073141 41434948  [O,......A1.CHICA]
F3D4120 032C4F47 15C10203 06324102 4C4C4144  [GO,......A2.DALL]
F3D4130 022C5341 0BC10203 08314102 2057454E  [AS,......A1.NEW ]
F3D4140 4B524F59 0203002C 410A0BC1 554F4343  [YORK,......ACCOU]
F3D4150 4E49544E 454E0847 4F592057 022C4B52  [NTING.NEW YORK,.]
F3D4160 0BC10203 41414104 454E0841 4F592057  [.....AAAA.NEW YO]
F3D4170 002C4B52 0BC10203 4343410A 544E554F  [RK,......ACCOUNT]
F3D4180 08474E49 2057454E 4B524F59 0203022C  [ING.NEW YORK,...]
F3D4190 41040BC1 08414141 2057454E 4B524F59  [...AAAA.NEW YORK]
F3D41A0 0203002C 4F0A29C1 41524550 4E4F4954  [,....).OPERATION]
F3D41B0 4F420653 4E4F5453 0203002C 53051FC1  [S.BOSTON,......S]
F3D41C0 53454C41 49484307 4F474143 0203002C  [ALES.CHICAGO,...]
F3D41D0 520815C1 41455345 06484352 4C4C4144  [...RESEARCH.DALL]
F3D41E0 002C5341 0BC10203 4343410A 544E554F  [AS,......ACCOUNT]
F3D41F0 08474E49 2057454E 4B524F59 BB360601  [ING.NEW YORK..6.]
Block header dump:  0x0100032b
 Object id on Block? Y
 seg/obj: 0x12367  csc: 0x00.15ba74  itc: 4  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000328 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0002.010.00000396  0x00c001b3.01f5.2a  ----    1  fsc 0x0003.00000000
0x02   0x000a.000.000002e0  0x00c00a9c.0090.0d  ----    1  fsc 0x0008.00000000
0x03   0x0007.01a.00000307  0x00c007bc.0163.2b  ----    1  fsc 0x0006.00000000
0x04   0x0001.015.000002ee  0x00c0008c.00fb.22  ----    1  fsc 0x0008.00000000
bdba: 0x0100032b
data_block_dump,data header at 0xf3d2294
===============
tsiz: 0x1f68
hsiz: 0x1a
pbl: 0x0f3d2294
     76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1dd5
avsp=0x1ef2
tosp=0x1f0b
0xe:pti[0] nrow=4offs=0
0x12:pri[0] offs=0x1e9e
0x14:pri[1] offs=0x1e0e
0x16:pri[2] offs=0x1de5
0x18:pri[3] offs=0x1dd5
block_row_dump:
tab 0, row 0, @0x1e9e
tl: 18 fb: --H-FL-- lb: 0x2 cc: 3
col  0: [ 2]  c1 0b
col  1: [ 2]  41 31
col  2: [ 8]  4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1e0e
tl: 16 fb: --H-FL-- lb: 0x3  cc: 3
col  0: [ 2]  c1 15
col  1: [ 2]  41 32
col  2: [ 6]  44 41 4c 4c 41 53
tab 0, row 2, @0x1de5
tl: 17 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 1f
col  1: [ 2]  41 33
col  2: [ 7]  43 48 49 43 41 47 4f
tab 0, row 3, @0x1dd5
tl: 16 fb: --H-FL-- lb: 0x4  cc: 3
col  0: [ 2]  c1 29
col  1: [ 2]  41 33
col  2: [ 6]  42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 811 maxblk 811

很明显，itl事务槽个数增加到了4个。同时在row记录上，lb:0X2也表示了该记录所对应事务的事务槽id。

事务提交后，事务槽会自动减少吗？不会！：

将4个session全部rollback后，观察数据块的itl事务槽个数：

*** 2015-10-09 14:53:12.105
Start dump data blocks tsn: 4 file#:4 minblk 811 maxblk 811
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16778027
BH (0x27FCC744) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x278AE000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0x287D806C,0x3D7951CC] lru: [0x27FCCE90,0x3C988024]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x287D7FF0) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2826C000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x287D8140,0x27FCC7C0] lru: [0x287D7BA4,0x27FE298C]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x287D80C4) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2826E000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: -1 tsn: 4 afn: 4 hint: f
  hash: [0x287E99FC,0x287D806C] lru: [0x287D9550,0x287D7E20]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x287E9980) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x28514000
  set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x28FF0F94,0x287D8140] lru: [0x28FDA1BC,0x287EDAC4]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x28FF0F18) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x28E30000
  set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x28FF1C00,0x287E99FC] lru: [0x28FF0EF0,0x287CAE44]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x28FF1B84) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x28E4E000
  set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2A7D4C40,0x28FF0F94] lru: [0x28FF1314,0x28FF1098]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2A7D4BC4) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2A1EE000
  set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2C7DD0C0,0x28FF1C00] lru: [0x28FDC2DC,0x28FDBB68]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2C7DD044) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2C32E000
  set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2BFC9F2C,0x2A7D4C40] lru: [0x2AFD2CF8,0x2C7DD864]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2BFC9EB0) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B84C000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x2BFCA424,0x2C7DD0C0] lru: [0x30FD346C,0x2BFC9E88]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2BFCA3A8) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B858000
  set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
  dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
  hash: [0x3D7951CC,0x2BFC9F2C] lru: [0x2C7F2478,0x2FFE4ED0]
  lru-flags: moved_to_tail on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0 lfb: 33
  flags:
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x0100032b (4/811)
scn: 0x0000.0015bbdb seq: 0x01 flg: 0x04 tail: 0xbbdb0601
frmt: 0x02 chkval: 0x4711 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0F3D2200 to 0x0F3D4200
F3D2200 0000A206 0100032B 0015BBDB 04010000  [....+...........]
F3D2210 00004711 00000001 00012367 0015BA74  [.G......g#..t...]
F3D2220 00000000 00320004 01000328 0000FFFF  [......2.(.......]
F3D2230 00000000 00000000 00000000 00008000  [................]
F3D2240 0010BEF3 00000000 00000000 00000000  [................]
F3D2250 00000000 00000000 00000000 00000000  [................]
        Repeat 3 times
F3D2290 00000000 00040100 001AFFFF 1EF21D79  [............y...]
F3D22A0 00001EF2 1DBB0004 1D911DA5 00001D79  [............y...]
F3D22B0 00000000 00000000 00000000 00000000  [................]
        Repeat 468 times
F3D4000 00000000 00000000 00000000 03002C00  [.............,..]
F3D4010 0A29C102 5245504F 4F495441 4206534E  [..).OPERATIONS.B]
F3D4020 4F54534F 03002C4E 051FC102 454C4153  [OSTON,......SALE]
F3D4030 48430753 47414349 03002C4F 0815C102  [S.CHICAGO,......]
F3D4040 45534552 48435241 4C414406 2C53414C  [RESEARCH.DALLAS,]
F3D4050 C1020300 43410A0B 4E554F43 474E4954  [......ACCOUNTING]
F3D4060 57454E08 524F5920 03042C4B 0229C102  [.NEW YORK,....).]
F3D4070 42063341 4F54534F 03012C4E 021FC102  [A3.BOSTON,......]
F3D4080 43073341 41434948 002C4F47 29C10203  [A3.CHICAGO,....)]
F3D4090 45504F0A 49544152 06534E4F 54534F42  [.OPERATIONS.BOST]
F3D40A0 032C4E4F 15C10203 06324102 4C4C4144  [ON,......A2.DALL]
F3D40B0 002C5341 15C10203 53455208 43524145  [AS,......RESEARC]
F3D40C0 41440648 53414C4C 0203012C 410229C1  [H.DALLAS,....).A]
F3D40D0 4F420633 4E4F5453 0203002C 53051FC1  [3.BOSTON,......S]
F3D40E0 53454C41 49484307 4F474143 0203012C  [ALES.CHICAGO,...]
F3D40F0 41021FC1 48430733 47414349 03002C4F  [...A3.CHICAGO,..]
F3D4100 051FC102 454C4153 48430753 47414349  [....SALES.CHICAG]
F3D4110 03012C4F 021FC102 43073141 41434948  [O,......A1.CHICA]
F3D4120 032C4F47 15C10203 06324102 4C4C4144  [GO,......A2.DALL]
F3D4130 022C5341 0BC10203 08314102 2057454E  [AS,......A1.NEW ]
F3D4140 4B524F59 0203002C 410A0BC1 554F4343  [YORK,......ACCOU]
F3D4150 4E49544E 454E0847 4F592057 022C4B52  [NTING.NEW YORK,.]
F3D4160 0BC10203 41414104 454E0841 4F592057  [.....AAAA.NEW YO]
F3D4170 002C4B52 0BC10203 4343410A 544E554F  [RK,......ACCOUNT]
F3D4180 08474E49 2057454E 4B524F59 0203022C  [ING.NEW YORK,...]
F3D4190 41040BC1 08414141 2057454E 4B524F59  [...AAAA.NEW YORK]
F3D41A0 0203002C 4F0A29C1 41524550 4E4F4954  [,....).OPERATION]
F3D41B0 4F420653 4E4F5453 0203002C 53051FC1  [S.BOSTON,......S]
F3D41C0 53454C41 49484307 4F474143 0203002C  [ALES.CHICAGO,...]
F3D41D0 520815C1 41455345 06484352 4C4C4144  [...RESEARCH.DALL]
F3D41E0 002C5341 0BC10203 4343410A 544E554F  [AS,......ACCOUNT]
F3D41F0 08474E49 2057454E 4B524F59 BBDB0601  [ING.NEW YORK....]
Block header dump:  0x0100032b
 Object id on Block? Y
 seg/obj: 0x12367  csc: 0x00.15ba74  itc: 4  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000328 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.0010bef3
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0100032b
data_block_dump,data header at 0xf3d2294
===============
tsiz: 0x1f68
hsiz: 0x1a
pbl: 0x0f3d2294
     76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1d79
avsp=0x1ef2
tosp=0x1ef2
0xe:pti[0] nrow=4offs=0
0x12:pri[0] offs=0x1dbb
0x14:pri[1] offs=0x1da5
0x16:pri[2] offs=0x1d91
0x18:pri[3] offs=0x1d79
block_row_dump:
tab 0, row 0, @0x1dbb
tl: 26 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 0b
col  1: [10]  41 43 43 4f 55 4e 54 49 4e 47
col  2: [ 8]  4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1da5
tl: 22 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 15
col  1: [ 8]  52 45 53 45 41 52 43 48
col  2: [ 6]  44 41 4c 4c 41 53
tab 0, row 2, @0x1d91
tl: 20 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 1f
col  1: [ 5]  53 41 4c 45 53
col  2: [ 7]  43 48 49 43 41 47 4f
tab 0, row 3, @0x1d79
tl: 24 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 29
col  1: [10]  4f 50 45 52 41 54 49 4f 4e 53
col  2: [ 6]  42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 811 maxblk 811

可见，事务结束后，i已经分配的itl事务槽并不会减少。并且row上的lb指向也被清除。