Killing Killed Session Without SPID

Hello!
Have you ever killed a session in database and the session doesn’t disappear? Or yet, you killed the SPID of the session in OS and the session still running in database, but now you cannot see the SPID?

Very weird, but it happens. First of all, let us point some interesting items:


– Killing sessions with “kill -9” is not recommended, first try to kill using “alter system kill session ‘&sid,&serial’ immediate;
– If you session was in a long DML transaction, is probable that it stills active doing some rollback. I’d recommend you to wait the rollback to be complete. You can speed up the process by setting parameter “fast_start_parallel_rollback” to “HIGH”. Give a shot with this first.
– In other situations, if you really need to do it for any reason, ok, then proceed with the kill -9 command for the SPID referent to the session you want to kill (the command below show to get the spin from a session, also showed in post Lock by DBLink – How to locate the remote session?).
– Buuut, if after kill the session (with kill -9 command) the session stills active and running in the database? Worst than that, what if now you cannot find the OS process (SPID) for this session, how to proceed?

This was my situation. You may also not that the column SERVER in v$session is set to PSEUDO. The reason for such session status and behavior is described here: Oracle Docs: Terminatin Sessions, take a look.

Ok, ok. But what can I do to definitely KILL this processes, once the alter system kill session was not effective nor the kill -9?

GrepOra have a trick for you! 😀

Here is a trick to list all OS process related to database that are not related to active sessions and kill them. Take a look:

greporadb1> select username,osuser,machine,logon_time,sid,serial#,inst_id from gv$session where module like 'TOAD%';
USERNAME OSUSER MACHINE LOGON_TIME SID SERIAL# INST_ID
--------------- --------------- ------------------------------ ------------------- ---------- ---------- ----------
APPLICATION_OWNER Administrator GREPORA\CFS-TEL0025 18/12/2016 17:05:52 33 45697 1
APPLICATION_OWNER Administrator GREPORA\CFS-TEL0025 18/12/2016 17:09:36 300 40805 1
APPLICATION_OWNER Administrator GREPORA\CFS-TEL0025 18/12/2016 17:06:20 579 39149 2

3 rows selected.

greporadb1> select p.spid,b.sid, p.pid, b.process as process_for_db_link, machine, logon_time
from v$session b, v$process p
where b.paddr=p.addr
and sid=&sid
/
Enter value for sid: 300
old 4: and sid=&sid
new 4: and sid=300

no rows selected

greporadb1> select 'kill -9 ' ||spid from v$process
where program not like '%(%)%'
and addr not in (select paddr from v$session)
and addr not in (select paddr from v$bgprocess)
and addr not in (select paddr from v$shared_server)
/
kill -9
kill -9 33401
kill -9 38758
3 rows selected.

Before killing the session, please check if not related to any vital background process (SMON, PMON, …):

[root@server1 ~]# ps -ef |grep 33401
oracle 33401 1 0 Dec18 ? 00:00:02 oraclegreporadb (LOCAL=NO)
root 81490 80780 0 17:14 pts/0 00:00:00 grep 33401
[root@server1 ~]# ps -ef |grep 38758
oracle 38758 1 0 Dec18 ? 00:00:00 oraclegreporadb (LOCAL=NO)
root 81624 80780 0 17:14 pts/0 00:00:00 grep 38758
[root@server1 ~]# kill -9 33401
[root@server1 ~]# kill -9 38758

In my case, as you can see, I only killed the sessions from server1 (node1 of greporadb). And after killing, only the session from node 2 is still connected. 🙂

greporadb1> select username,osuser,machine,logon_time,sid,serial#,inst_id from gv$session where module like 'TOAD%';
OSUSER MACHINE LOGON_TIME SID SERIAL# INST_ID
--------------- ------------------------------ ------------------- ---------- ---------- ----------
Administrator GREPORA\CFS-TEL0025 18/12/2016 17:06:20 579 39149 2
1 row selected.

Nice hãn?
I expect you don’t need that, but if you need, I hope it helps you. 🙂

See you!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s