Oracle Table Deadlock Prevention

Oracle Table Deadlock Prevention

The following instructions will show how to change a table parameter so that future deadlocks can be prevented during loading or calculation runs.
1. Log into Oracle Database server as Oracle user.
2. Launch SQLPlus by typing “sqlplus /nolog”.
3. Type “select table_name, owner, ini_trans, max_trans from dba_tables where table_name = ‘/B20C/AR4000’;”. Table name may be substituted by other tables facing deadlock issues.
4. The SQL statement above will show the current parameter value of the table. Default value is less than 10.
5. Change the parameter to the desired value (same number as loading/calculation processes whichever is higher). In our case of having 70 processes for calculation, we choose the value 70. Type “alter table sapsr3.”/B20C/AR4000” initrans 70;”.
6. Confirm by typing “select table_name, owner, ini_trans, max_trans from dba_tables where table_name = ‘/B20C/AR4000’;” again to check the new value.
7. Do the same for all the table’s indexes. See next section.





The following instructions are similar to above but now they apply to the table’s indexes.

1. Log into Oracle Database server as Oracle user.
2. Launch SQLPlus by typing “sqlplus /nolog”.
3. Type “select index_name, owner, ini_trans, max_trans from dba_indexes where table_name = ‘/B20C/AR4000’;”. Table name may be substituted by other tables facing deadlock issues.
4. The SQL statement above will show the current parameter value of the table’s indexes. Default value is less than 10.
5. Change the parameter to the desired value (same number as loading/calculation processes whichever is higher). In our case of having 70 processes for calculation, we choose the value 70. Type “alter index sapsr3.”/B20C/AR4000~0” initrans 70;”.
6. Confirm by typing “select index_name, owner, ini_trans, max_trans from dba_indexes where table_name = ‘/B20C/AR4000’;” again to check the new value.