Altering Tablespace Readonly Hangs (Doc ID 289867.1)

Last updated on SEPTEMBER 15, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 and later   [Release: 10.1 and later ]
Information in this document applies to 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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms