JL Computer Consultancy

Acknowledgements - Nigel Noble of Churchill Insurance (UK) first brought this anomaly, and the surprising work-around, to the attention of the Oracle community several years ago.

When rebuilding an entire database, for example when moving from one platform to another, you may decide to save a bit of time by building multiple tablespaces in parallel starting several independent sessions with commands like:

        create tablespace vg_a datafile '/vg_a/vg_a.dbf' size 20001M;

        create tablespace vg_b datafile '/vg_b/vg_b.dbf' size 20001M;

Unfortunately if you try this you will find that the first session to start runs quite happily whlst all the other sessions hang around doing nothing. When the first tablespace is completed another session will take over and start to run.

Investigating v$session_wait, and v$session_event, you will find that all the other sessions are waiting for a 'Row-exclusive (mode 3)' row cache lock on the dc_free_extents rowcache with a timeout on the wait is 3 seconds per cycle

Since it can take in the order of 10 minutes per gigabyte to build a tablespace it would be nice to be able to get some parallel activity going, especially when trying to build a 500 GB database. It is possible, and the method is bizarre.

Step 1: Create all the tablespaces you need, but make them tiny - 2 blocks each if you fancy

Step 2: Drop all the tablespaces you have just created, and if they are on file system delete the files.

Step 3: Start off scripts to recreate the tablespaces at the correct size, but make sure the create statements run in reverse order to the order you first created the tablespaces.

Don't ask why this work-around works, it just does. The most recent version of Oracle I have tried this on is 7.3.4.2, and the problem still exists and the fix still works. Fortunately this procedure is not needed for Oracle 8 where parallel creation of tablespaces works properly.