1) Depending on the number of transactions in the table we need to alter the value of INITRANS.
here it has been changed to 50:
alter table INITRANS 50;
2) Then re-organize the table using move (alter table move;)
3) Then rebuild all the indexes of this table as below
alter index rebuild INITRANS 50;
Increase PCTFREE
If the issue is not resolved by increasing INITRANS then try increasing PCTFREE. Increasing PCTFREE holds more
space back and so spreads the same number of rows over more blocks. This means that there are more ITL slots
available overall :
1) Spreading rows into more number of blocks will also helps to reduce this wait event.
alter table PCTFREE 40;
2) Then re-organize the table using move (alter table service_T move;)
3) Rebuild index
alter index index_name rebuild PCTFREE 40;
A Combination of increasing both INITRANS and PCTFREE
1) Set INITRANS to 50 pct_free to 40
alter table PCTFREE 40 INITRANS 50;
2) Re-organize the table using move (alter table move;)
3) Then rebuild all the indexes of the table as below
alter index rebuild PCTFREE 40 INITRANS 50;