How to Clear shared server sessions from Oracle database

Tried to kill shared server session in db with kill immediate, even after multiple attempts i can still see session status is active,

Normally it will terminate when job restart from background or apps, 

if session using dedication connection we can directly kill os pid, Lets see how we can do it with shared server settings 

Clear sid with kill immediate option:

SQL> select sid,serial#,inst_id,status from gv$session where sid='7407';

   SID    SERIAL#    INST_ID STATUS

------ ---------- ---------- --------

  7407      39883          2 ACTIVE

SQL> ALTER SYSTEM KILL SESSION '7407,39883,@2' immediate;

System altered.

SQL> select sid,serial#,inst_id,status from gv$session where sid='7407';

   SID    SERIAL#    INST_ID STATUS

------ ---------- ---------- --------

  7407      39883          2 ACTIVE

--> Session status is active after killing 

Find OS process id from sid:

select a.sid, a.serial#,a.username, a.osuser, b.spid

from v$session a, v$process b

where a.paddr= b.addr

and a.sid='&sid'

   SID    SERIAL# USERNAME        OSUSER          SPID

------ ---------- --------------- --------------- ------------------------

  7407      39883 MANAGE          oracle          115907

  

  

SQL> !ps -ef | grep 115907

oracle   115907      1 13 Jun04 ?        8-17:45:14 ora_s000_prddb012

oracle   140285 139222  0 22:23 pts/0    00:00:00 /bin/bash -c ps -ef | grep 115907

oracle   140287 140285  0 22:23 pts/0    00:00:00 grep 115907

we can see session using shared server process ( from extension ora_s00*), 

need to be careful while killing it from os level since one shared server process will work for multiple sessions, Let's check if any other sessions are using same shared server process or not. 

Check session details from pid:

select p.spid,s.sid, s.serial#,s.username, s.osuser

from gv$session s, gv$process p

where s.paddr= p.addr

and p.spid='&spid'

order by p.spid;

SPID                        SID    SERIAL# USERNAME             OSUSER

------------------------ ------ ---------- -------------------- ---------------

115907                     7407      39883 MANAGE               oracle

also make sure process is not active/running by using strace command:

strace command:

$strace -o strace_output_115907.txt -p 115907

$tail -90 strace_output_115907.txt

no other session is related with shared server process,we are good to kill it pid  

Kill OS PID:

$Kill -9 115907

Check Sid status: it's gone

SQL>  select sid,serial#,inst_id,status from gv$session where sid='7407';

no rows selected

PMON will start new shared server process immediately 

[oracle ~]$ ps -ef | grep ora_s000

oracle   149329      1  0 22:27 ?        00:00:00 ora_s000_prddb012

oracle   150183  84710  0 22:27 pts/0    00:00:00 grep --color=auto ora_s000