Altering Tablespace Readonly Hangs

The information in this document applies to: 
Oracle Server - Enterprise Edition - Version: 10.1.0.2
This problem can occur on any platform.
Symptoms
In Oracle 10g and Oracle 9.2.x also, the command “ALTER TABLESPACE READ ONLY”
waits until all the transactions are completed even though the ALTER command was started before some active transactions.



Here is the testcase: —>
Steps required to reproduce:
1. session 1: insert into table A (tablespace TS1), NO COMMIT at this time
2. session 2: alter tablespace TS2 read only;
3. session 3: alter tablespace TS3 read only;
4. session 4: insert into table B (tablespace TS4), NO COMMIT at this time
5. session 1: commit
So in this case we expect session 2 and session 3 to advance after commit
in session 1. but obviously session 4 blocks session 3.
After session 1 commits, Session 2 will advance but session 3 will hang until session 4 is completed.
Cause
This behaviour is due to AT enqueue acquired by the “Alter tablespace read only” command .
When you issue “ALTER TABLESPACE xxx READ ONLY” we do this:
- acquire AT lock exclusive
- set datafile not modifyable
- Wait on any active TX to commit
(If a TX touches the tablespace at this time it gets ORA-372)
- Finish off updating TS to read only
- Release AT lock
Now in the scenario above you have issued 2 “ALTER TABLESPACE xxx READ ONLY”
commands but although the second command has been issued it has not started as it needs
the AT lock before it can start anything.
Hence session 3 waits on the AT lock.New transactions can be begin and do work.
Only when session 2 completes making Tablespace TS2 R/O does it release the
AT lock which session 4 can then acquire and proceed.
Fix
This is working as intended and as per design.
References
Bug 3752361 - Alter Tablespace Read Only Command Hangs Until All Transactions Are Completed