ITC value is higher than MAXTRANS for index blocks

  • From: Sami Seerangan <dba.orcl@xxxxxxxxx>
  • To: oracle list <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 24 Nov 2005 16:47:47 -0500

Dear All,

Oracle  3-Node RAC,Sun OS 2.9
Block size 16K

LMT with ASSM.

create table t1(c1 varchar2(4000),c2 number);
alter table t1 add constraint t1_pk primary key (c1) using index initrans 2
maxtrans 4 pctfree 99;

insert into t1 values(lpad('A',3999,'A'),1);

1 row inserted.

insert into t1 values(lpad('B',3999,'A'),1);

1 row inserted.

insert into t1 values(lpad('C',3999,'A'),1);

1 row inserted.
insert into t1 values(lpad('D',3999,'A'),1);

Session hangs..... because of ITL wait. Not an issue

At this point we took block dump for an index using below statement;
select 'alter system dump datafile 9 block min '||c1||' block max '||c2||';'
from (select block_id c1,(block_id + blocks-1) c2 from dba_extents
where segment_name='T1_PK')
SQL> /

alter system dump datafile 9 block min 23889 block max 23896;

The block dump shows ITC value as 255 for 3 out of 8 blocks even though the
maxtrans value
is defined as 4.

Is it possible to have ITC value higher than MAXTRANS value?

Other related posts: