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

Last updated on MARCH 03, 2022

Applies to:

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

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