Tuesday, August 18, 2009

alter tablespace read only hangs.

# I ran the following, but it hung
alter tablespace ORDERAUDIT_PAR10_06 read only;

# I ran diagnostics to identify the transctions (this is not really needed)
SQL> SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND SQL_TEXT LIKE 'alter tablespace%'; 2 3 4

SQL_TEXT
--------------------------------------------------------------------------------
SADDR
----------------
alter tablespace ORDERAUDIT_PAR10_06 read only
070000010EB2D260


SQL> SELECT SES_ADDR, START_SCNB
FROM V$TRANSACTION
ORDER BY START_SCNB; 2 3

SES_ADDR START_SCNB
---------------- ----------
070000010EAF4420 453671129
070000010EB2D260 455278874
070000010EB2D260 455278877

SQL> 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 2 3 4
5 ;

SES_ADDR USERNAME
---------------- ------------------------------
MACHINE
----------------------------------------------------------------
070000010EAF4420 BKO_SERVER
CMC\CFDFXNFR-APP04

070000010EB2D260 SYS
test-oranode-40

070000010EB2D260 SYS
test-oranode-40

# The blocking session was clearly identified in dba_blockers view. (we could use this to schedule an application restart)
1* select sid, username, program, machine from v$session where sid in (select holding_session from dba_blockers)
SQL> /

SID USERNAME
---------- ------------------------------
PROGRAM
------------------------------------------------
MACHINE
----------------------------------------------------------------
1586 BKO_SERVER
jCFDFXNFRBackoffice1.exe
CMC\CFDFXNFR-APP04

# I killed the session, and the "alter tablespace" command completed.
SQL> select 'alter system kill session '''||sess.sid||', '||sess.serial#||''';' from v$session sess where sid in (select holding_session from dba_blockers);

'ALTERSYSTEMKILLSESSION'''||SESS.SID||','||SESS.SERIAL#||''';'
--------------------------------------------------------------------------------
alter system kill session '1586, 1177';

SQL> alter system kill session '1586, 1177';

System altered