Kill Dedicated Server Sessions
kill session immediate statement does not guarantee that the specified session will be killed immediate, especially when the session is active and busy. In some cases, the marked for kill session is not terminated eventually, you should kill the process by yourself at OS-level to release locks or memory.
For identifying the process id at OS-level that we are going to kill, you can specify the username like the following example statement.
SQL> select s.inst_id, s.sid, s.serial#, s.status, p.spid os_process from gv$session s, gv$process p where s.inst_id=p.inst_id and s.paddr = p.addr and s.username='HR' order by 1,2;
INST_ID SID SERIAL# STATUS OS_PROCESS
---------- ---------- ---------- -------- ------------
1 79 30889 INACTIVE 21477
1 159 20538 ACTIVE 22010
1 179 1776 INACTIVE 26935
2 136 9934 ACTIVE 16594
Next, we use "ps -ef" to confirm the process id. If the process shows as "LOCAL=NO" as following example, it's a dedicated server process and is connected from a remote machine.
$ ps -ef | grep 20538
oracle 20538 1 0 Oct 02 ? 40:44 oracleORCL (LOCAL=NO)
...
$ kill -9 20538
We have killed a dedicated session. To kill all LOCAL=NO sessions, we have some way to do it.
Kill Shared Server Sessions
In one case I met before, there were three sessions blocking other sessions enqueued behind, although I issued kill session immediate statements explicitly several times on them, the status of them were still ACTIVE.
SQL> alter system kill session '43,2049' immediate;
System altered.
SQL> alter system kill session '50,1242' immediate;
System altered.
SQL> alter system kill session '79,466' immediate;
System altered.
SQL> select status from v$session where sid=43;
STATUS
--------
ACTIVE
SQL> select status from v$session where sid=50;
STATUS
--------
ACTIVE
SQL> select status from v$session where sid=79;
STATUS
--------
ACTIVE
Those sessions could be stuck for some reasons, maybe they were waiting for locks released or database link responses.
After checking the OS, the console showed that the sessions were all from shared server processes:
$ ps -ef | egrep '21555|21567|21525'
oracle 21555 1 0 12:51:07 ? 5:21 ora_s023_ORCL
oracle 21525 1 0 12:51:06 ? 0:15 ora_s015_ORCL
oracle 21567 1 0 12:51:08 ? 0:04 ora_s026_ORCL
...
As you can see, the shared server processes are named as ora_snnn_$ORACLE_SID.
Since shared server processes are shared by other sessions, we should be careful before actually killing them. But I think they were occupying the whole shared server processes.
Let's kill them at OS-level:
$ kill -9 21555
$ kill -9 21525
$ kill -9 21567
$ ps -ef | egrep 's023|s015|s026'
oracle 19992 1 0 19:17:29 ? 0:00 ora_s026_ORCL
oracle 19936 1 0 19:16:28 ? 0:00 ora_s023_ORCL
oracle 19932 1 0 19:16:28 ? 0:00 ora_s015_ORCL
...
Please notice that, the above shared server processes all have new process id, they are regenerated by PMON in a very short time (in few seconds).