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 Braunstein’s 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:
- change the
INI_TRANSvalue - 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?