Oracle – why do I need INITRANS if I have MAXTRANS?

All we need is an easy explanation of the problem, so here it is.

Probably is a dummy question, but I still don’t understand why do I need the INITRANS parameter if I already have the MAXTRANS parameter. I have understand that, the default INITRANS for tables is 1 and index is 2, but also, we have the MAXTRANS that the default is 255 (I guess it depend of the block size). and, because we have 255 in MAXTRANS, the number of ITL can grow dynamically as needed, so, why do I need to care about INITRANS? can someone explain it?

I need to learn because I am having several event in a database: enq: TX - allocate ITL entry

Also, when I change the INITRANS of an existing table, do I need to move the table as well to affect all existing data block in that table? or just change the INITRANS is enough?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

ITL cannot grow in a block that is already full.

INITRANS sets a guaranteed minimum to its size.

After changing INITRANS on a table, you need to move it so its blocks will be reorganized to reserve the required space for ITL.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply