Alter Tablespace Read Only Hanging When There Are Active TX In Any Tablespace (Doc ID 554832.1)

Last updated on SEPTEMBER 15, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.1.0.6 - Release: 8.1.7 to 11.1
Information in this document applies to any platform.

Symptoms

Alter tablespace <ts name> read only hangs when there is active transaction in any tablespace till all active transactions commit or rollback.

Test case
=========
The steps to reproduce the problem:
1. Insert a row into foo table and don't commit the transaction. foo table
resides in USERS tablespace. 

SQL> connect kirti
SQL> desc foo
Name                                                      Null?    Type
----------------------------------------- -------- ----------------
ABC                                                                    NUMBER (38)
DEFNOT                                               NULL   NUMBER (38)
GHI                                                                     NUMBER(38) 

SQL> insert into foo values (1,2,3); 

1 row created. 

2. Login the other session and alter tablespace users2 read only. 

SQL> alter tablespace users2 read only; 

This session will hang till the insert transaction commit or rollback and
once committed or rolled back the users2 tablespace altered immediately.

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