Friday, June 8, 2012

Make Tablespace Read Only takes too long

Many time when making a tablespace read only it can seem that the operation has hanged. Usually your session is waiting for transaction(s) to finish that have started prior the alter tablespace ... read only statement. So in order to decide what to do you must find those transactions and their corresponding sessions and then decide what to do. Here is the procedure :

- Find the your session address

SELECT B.sid,
  B.SERIAL# ,
  a.SQL_TEXT,
  B.SADDR ,
  B.INST_ID,
  B.MACHINE,
  b.module
FROM V$SQLAREA a,
  V$SESSION B
WHERE a.ADDRESS = B.SQL_ADDRESS
AND lower(SQL_TEXT) LIKE 'alter tablespace%read only%' ;

- Find transactions that started prior your session (note that the order is on START_SCNB)


SELECT S.SID,
  S.SERIAL#
  T.SES_ADDR,
  S.USERNAME,
  S.MACHINE,
  t.START_SCNB
FROM V$SESSION S,
  V$TRANSACTION T
WHERE T.SES_ADDR = S.SADDR
ORDER BY t.START_SCNB;

- Decide what to do with the sessions