Skip to content

How to Kill Sessions Running on Shared Server Processes

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;

---------- ---------- ---------- -------- ------------
         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;


SQL> select status from v$session where sid=50;


SQL> select status from v$session where sid=79;


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).

Leave a Reply

Your email address will not be published. Required fields are marked *