Oracle Database Alter Tablespace Read Only Hangs

** Credit K.ittichai  http://oraexplorer.com/

I found this tip from Oracle document while looking for a solution for session hung at the SQL prompt after issuing “alter tablespace read only”. Usually “alter tablespace read only” executes very quickly. However, the likelihood cause of waiting for tablespace to become read-only is due to existing in-flight transactions started before “alter tablespace read only” are still running.
To identify these transactions that are preventing the read-only tablespace is to first identify the “alter tablespace read only” session.

SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND SQL_TEXT LIKE 'alter tablespace%';

SQL_TEXT                                 SADDR
---------------------------------------- ----------------
alter tablespace tbs_tts1 read only      0000040634C0D8B8

Based on the identified session address and start SCN number, we can find the earlier executions before the read-only statement by querying the v$TRANSACTION order by ascending start SCN.

SELECT SES_ADDR, START_SCNB
FROM V$TRANSACTION
ORDER BY START_SCNB;

SES_ADDR         START_SCNB
---------------- ----------
0000040634C254F8 2976616884    --> Waiting on this transaction
0000040634C0D8B8 2980274305    --> alter tablespace read only
0000040634C53858 2980283454

From the session address of the blocking transaction, we then can find information about that session.

SELECT T.SES_ADDR, S.USERNAME, S.MACHINE
FROM V$SESSION S,  V$TRANSACTION T
WHERE T.SES_ADDR = S.SADDR
ORDER BY T.SES_ADDR

SES_ADDR         USERNAME             MACHINE
----------------  --------------------  ------------------
0000040634C254F8  RT_ADMIN          isdweb1

Once the session is identified, an appropriate action whether to terminate this session can be decided. In our case, it was just a run-away session. Once terminated, alter tablespace read only completed right away.
Reference: Oracle Database Administrator’s Guide 11g Release 1 (11.1)