Today I re-learned a nice option to overcome ORA-60 problems with parallel data loading scenarios.
As most of you will know (from reading note “84348 – Oracle-Deadlocks, ORA-00060” or Michael Braunsteins Blogs Chasing Oracle deadlocks part I – row level locks/Chasing Oracle deadlocks part II – block level locks or some other resource) the problem here is caused by Oracle space management on block-level.
There is a specific block-level storage setting that needs to be changed (INI_TRANS) in order to guarantee multiple parallel transactions in an already used block.
The key point to this change is, that it’s not sufficient to run the ALTER TABLE/ALTER INDEX command since this will only change the setting for new blocks. To also cover the blocks already in use, the table/index needs to be reorganized or rebuild.
Usually, this would have been a two-step procedure:
1) change the INI_TRANS value
2) perform the reorg with BRSPACE
The thing I re-learned just five minutes ago is:
BRSPACE has a switch for this – so you can do it all at once.
The following command will reorganize table “/BIC/FMYTESTCUBE” into the same tablespace and set the INI_TRANS parameters for the indexes (-SII) and for the table (-STI) to 20.
brspace -u / -f tbreorg -t "/BIC/FMYTESTCUBE" -STI 20 -SII 20
Nice, isn’t it?