Tuesday, June 15, 2010

ALTER SYSTEM KILL Session Marked for Killed Forever

You have a session that you have killed, but it seems as though it will not go away:

SQL> ALTER SYSTEM KILL SESSION 'sid, serial#';

SQL> SELECT STATUS, USERNAME FROM V$SESSION;

status killed
username username

You have issued this several times and it seems it still is marked as killed, but has a lock on the existing table.
In order to determine which process to kill:

a) On a Unix platform:
SQL> SELECT SPID
FROM V$PROCESS
WHERE NOT EXISTS (SELECT 1
FROM V$SESSION
WHERE PADDR = ADDR);

% kill spid
where spid is the output from the above query

b) On a Windows platform:
SQL> SELECT SPID, OSUSER, S.PROGRAM
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR;
Then use the orakill utility at the DOS prompt:
D:\> orakill db_sid spid

where
db_sid = the Oracle instance name (ORACLE_SID)
spid = the thread id of the thread to kill

Explanation:
============
PMON has not yet cleaned up the process and this may not happen for several hours.
By finding the spid you can then force the process to be killed instead of
waiting for PMON to wake up and kill it.
When issuing the 'kill' command be sure that you kill "DEDICATED SERVER PROCESSES", those called:
oracle (local=NO)
where is the ORACLE_SID.
Be sure you do not kill processes such as:

ora_d000_
ora_s000_
ora_pmon_

No comments: