Tip#20 Session Marked for Kill Forever

I had a situation recently where our junior DBA accidentally killed an MV refresh job session and session killed was 'marked for kill' forever i.e. it wont release the resource (in this case lock) hence any subsequent refresh were also failing.

Lock holding session can be determined by,

select SID from v$lock where ID1 = (select object_id from dba_objects where object_name = *Object name* and OWNER = *Owner*)

Also any subsequent try to kill/disconnect session using alter session will result in ORA-00031: session marked for kill. So only way to release resources is to kill the process at OS level and let the PMON do the cleanup.

Now since sessoin is already killed and we are not yet using 11g (which gives you OS PID after killing as well), I tried getting OS PID using following SQL (Also suggest to read Metalink Doc ID 1020720.102) i.e.

SQL> SELECT spid FROM v$process WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr) AND spid IS NOT NULL;

unfortunately this didnt help as PID returned was not JOB process, you can check on Linux using,

% ps -ef | grep spid

The process I was expecting to be something like ora_j00x_sid but didnt found any so search for OS PID was still on.

Since we still have an entry in v$session with 'KILLED' status, we decided to get a 'Logon time' and see if we can match that with OS PID for same time and with command format 'ora_j00x_sid'. And fortunately we had hit the bulls eye we had only one OS PID with same time and expected command so we could just easily nailed it. So we killed the process at OS level

% kill spid

and after few moments we could see the lock was released!