ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于initrans参数

关于initrans参数

原创 Linux操作系统 作者:NinGoo 时间:2019-02-20 16:09:04 0 删除 编辑

在Oracle9i OCP教材上有这么一段话:

INITRANS, which defaults to 1 for a data segment and 2 for an index segment, guarantees a minimum level of concurrency.

这段话是错误的,对于table,initrans默认是2,最小值也是2,某些情况下,默认值和最小值都是3。


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

1.普通的table的initrans默认值为2

SQL> create table t1(id int,name varchar2(20));

Table created.

SQL> insert into t1 values(1,'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select file_id,block_id from dba_extents where segment_name='T1' and owner=
user;

FILE_ID BLOCK_ID
---------- ----------
7 121

SQL> alter system dump datafile 7 block 124;

System altered.

查看对应的trace文件可以发现:

Start dump data blocks tsn: 7 file#: 7 minblk 124 maxblk 124
buffer tsn: 7 rdba: 0x01c0007c (7/124)
scn: 0x0000.00098e4e seq: 0x02 flg: 0x00 tail: 0x8e4e0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01c0007c
Object id on Block? Y
seg/obj: 0x67de csc: 0x00.98e4e itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1c00079 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

2.即使指定initrans=1,实际上的initrans还是2

SQL> create table t2(id int,name varchar2(20)) initrans 1;

Table created.

SQL> insert into t2 values(1,'a');

1 row created.

SQL> select file_id,block_id from dba_extents where segment_name='T2' and owner=
user;

FILE_ID BLOCK_ID
---------- ----------
7 129

SQL> alter system dump datafile 7 block 132;

System altered.

trace文件如下:

Start dump data blocks tsn: 7 file#: 7 minblk 132 maxblk 132
buffer tsn: 7 rdba: 0x01c00084 (7/132)
scn: 0x0000.00098f3a seq: 0x02 flg: 0x00 tail: 0x8f3a0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01c00084
Object id on Block? Y
seg/obj: 0x67df csc: 0x00.98f3a itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1c00081 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

3.CTAS建的table的initrans默认值为3

SQL> select file_id,block_id from dba_extents where segment_name='T3' and owner=
user;

FILE_ID BLOCK_ID
---------- ----------
7 1105

SQL> alter system dump datafile 7 block 1108;

System altered.

Start dump data blocks tsn: 7 file#: 7 minblk 1108 maxblk 1108
buffer tsn: 7 rdba: 0x01c00454 (7/1108)
scn: 0x0000.00098faa seq: 0x02 flg: 0x04 tail: 0x8faa0602
frmt: 0x02 chkval: 0x872f type: 0x06=trans data
Block header dump: 0x01c00454
Object id on Block? Y
seg/obj: 0x67e0 csc: 0x00.98fa9 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1c00451 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00098fa9
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

4.CTAS建的table的initrans最小值为3

SQL> create table t4 initrans 1 as select * from t1;

Table created.

SQL> select file_id,block_id from dba_extents where segment_name='T4' and owner=
user;

FILE_ID BLOCK_ID
---------- ----------
7 1113

SQL> alter system dump datafile 7 block 1116;

System altered.

Start dump data blocks tsn: 7 file#: 7 minblk 1116 maxblk 1116
buffer tsn: 7 rdba: 0x01c0045c (7/1116)
scn: 0x0000.00098fe9 seq: 0x02 flg: 0x04 tail: 0x8fe90602
frmt: 0x02 chkval: 0x872e type: 0x06=trans data
Block header dump: 0x01c0045c
Object id on Block? Y
seg/obj: 0x67e1 csc: 0x00.98fe8 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1c00459 ver: 0x01
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00098fe8
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

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

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    80
  • 访问量
    57180