Oleksandr Alesinskyy, March 03, 2016 - 4:23 pm UTC
https://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses007.htm INITRANS integer
Specify the initial number of concurrent transaction entries allocated within each data block allocated to the database object. This value can range from 1 to 255 and defaults to 1, with the following exceptions:
The default INITRANS value for a cluster is 2 or the default INITRANS value of the tablespace in which the cluster resides, whichever is greater. The default value for an index is 2.
March 04, 2016 - 1:49 am UTC
I think thats probably a documentation bug. Even going back to version 9, the default storage clause for tablespace does not appear to include initrans
STORAGE
( { INITIAL integer [ K | M ]
| NEXT integer [ K | M ]
| MINEXTENTS integer
| MAXEXTENTS { integer | UNLIMITED }
| PCTINCREASE integer
| FREELISTS integer
| FREELIST GROUPS integer
| OPTIMAL [ integer [ K | M ] | NULL ]
| BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
}
[ INITIAL integer [ K | M ]
| NEXT integer [ K | M ]
| MINEXTENTS integer
| MAXEXTENTS { integer | UNLIMITED }
| PCTINCREASE integer
| FREELISTS integer
| FREELIST GROUPS integer
| OPTIMAL [ integer [ K | M ] | NULL ]
| BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
)
Cheers,
Connor
Does initrans of 2 causes deadlock situations?
Jeff, November 24, 2016 - 5:17 am UTC
Hello,
First of all, Thank you for provinding wonderful solutions. We have a table which has lob column and we purge the records from this table from 5 parallel sessions. It is simple delete from table where rowid between min_rowid and max_rowid. When we run this, 2 sessions will always get into deadlock situation. All the indexes and the table have initrans of 10. Except for lobindex which has 2. I am puzzled on what could have caused the deadlock in this situation. It is RAC database and 11.2.0.4. Everytime i check the ASH data, it shows the wait event is enq: tx row lock wait event and object is lobindex. Please throw some light on this
November 24, 2016 - 12:48 pm UTC
What does the deadlock trace show?