My Oracle Support Banner

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

Last updated on JUNE 05, 2019

Applies to:

Oracle Database - 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 test1 table and don't commit the transaction. test1 table
resides in USERS tablespace. 

SQL> connect <username>
SQL> desc test1
Name                                                      Null?    Type
----------------------------------------- -------- ----------------
ABC                                                                    NUMBER (38)
DEFNOT                                               NULL   NUMBER (38)
GHI                                                                     NUMBER(38) 

SQL> insert into test1 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.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.