Oracle challenge

During DataObjects.Net development I've faced a very annoying Oracle "feature". It is a well-known fact that Oracle implements a multiversion concurrency model. It keeps old versions of modified data (in so called "undo segments") for transactions that need them. I'm omitting the explanation of Oracle MVCC concepts as they are described in details in appropriate books. The rest of the post assumes you are familiar with them. I'm going to tell you about one not so common pitfall when using Oracle.

Here is the "feature": sometimes during index restructuration undo data becomes unavailable. Transaction that runs in serializable isolation level and requests data that is somehow related to such index gets ORA-08177: can't serialize access for this transaction error. A good explanation can be found in this thread. This can even happen when only one session is accessing database.

What is the solution? Recent versions of Oracle supports rowdependencies clause for create table statement. By default Oracle retains SCN for each data block. So all rows in the block have the same SCN. rowdependencies clause slightly changes structure of the table preserving SCN for each row independently. Using rowdependencies clause decreases chance of getting ORA-08177.

However, I'm still getting this error during heavy inserts, so I'm forced to use read committed isolation level instead.