It has to do with the fact that a drop tablespace command does not in fact drop the tablespace from the data dictionary - it only marks it as INVALID. You can verify this by selecting from TS$ directly. Therefore when "creating" a tablespace that previously existed there is no need to raise the high-water mark of the C_TS# cluster, as would otherwise be the case. Not that raising a high-water mark would normally take a dc_free_extent enqueue, however extending a segment would, and one implies a risk of the other. Serializing tablespace creation on the row cache enqueue was a short-cut. It saved the Oracle developers from having to allow for a possible failure to extent C_TS# once all the hard work of "zeroing" the datafile(s) had been done.
The following mail i got from my friend. I am not using oracle version 7. I did not even try whether it works as he said. Just curious to know, can you think of why it works!!
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 an exclusive row cache lock on the dc_free_extents rowcache with a timeout on the wait of 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.