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) - Making a Tablespace Read-Only
