Kill Oracle Sessions

                        One day My manager fired one sql statement, it got stuck and not moving further. He called up me and asked to kill Oracle Session. I immediately killed his oracle session using SQL*PLUS approach. I would like to share what I did . Here I am giving the procedurehow to kill Oracle Sessions using SQL* Plus, Using Kill Command ( In Unix/Linux ) and orakill Commands ( Windows NT). 

(a) SQL* PLUS:       

In order to use SQL*PLUS Approach, first we need to find out the SID, SERIAL# of the Session which we want to kill. Then issue the following command to kill the session 

SQL> alter system kill session ‘SID, SERIAL#’; 

 When we issue the above statement, It terminates a session, rolls back ongoing transactions, releases all session locks, frees all session resources.  If the session is performing some activity that must be completed (e.g. waiting for a reply from a remote database or rolling back a transaction), Oracle waits for this activity to complete, kills the session then returns control. If the wait lasts for 60 seconds then Oracle marks the session to be killed, and returns control with a message that the session is marked to be killed. It then gets killed when the activity is complete.

  (b) Using KILL Command (Unix/Linux)     

    To kill the sessions using KILL Command, we need to find out the SPID ( Server Process ID) of the Oracle Session. Then issue the KILL Command

 $ kill -9 <SPID> 

(c) Using ORAKILL Command ( Windows)      

To kill the sessions using ORAKILL Command ( Windows), we need to find out the SPID of Session and ORACLE_SID of your Oracle Database. Then issue ORAKILL Command 

C:\> orakill <ORACLE_SID>  <SPID> 

Here find the practical approach 

 Using SQL*PLUS 

 SQL> select username,sid,serial#,terminal from v$session; 

USERNAME    SID    SERIAL# TERMINAL

—————————— ———- ————-

SCOTT                           134      47747 BSR       

SYS                                   147      46965 pts/0                     

                                          150          2 UNKNOWN                      

                                          151          4 UNKNOWN                              

                                          154          1 UNKNOWN                                      

                                          167          1 UNKNOWN                                

                                          168          1 UNKNOWN                              

                                          169          1 UNKNOWN                                  

                                          170          1 UNKNOWN 

18 rows selected. 

SQL> alter system kill session ‘134,47747’ immediate; 

System altered.

================================================================ 

Using Unix KILL Command:

 ============================== 

SQL> SELECT s.sid,       p.spid,       s.osuser,       s.programFROM   v$process p,       v$session sWHERE  p.addr = s.paddr;

SID SPID         OSUSER                         PROGRAM

———- ———— ——————————     

170 15771        oracle                         oracle@oraprod (PMON)      

 169 15773        oracle                         oracle@oraprod (PSP0)    

 168 15775        oracle                         oracle@oraprod (MMAN)      

167 15777        oracle                         oracle@oraprod (DBW0)      

 166 15779        oracle                         oracle@oraprod (LGWR)    

 165 15781        oracle                         oracle@oraprod (CKPT)     

 164 15783        oracle                         oracle@oraprod (SMON)    

 163 15785        oracle                         oracle@oraprod (RECO)     

 162 15787        oracle                         oracle@oraprod (CJQ0)      

 161 15789        oracle                         oracle@oraprod (MMON)     

 160 15791        oracle                         oracle@oraprod (MMNL)    

 138 16425        oracle                         oracle@oraprod (J000)     

 154 15799        oracle                         oracle@oraprod (QMNC)    

 147 16259        oracle                         sqlplus@oraprod (TNS V1-V3)      

 150 15807        oracle                         oracle@oraprod (q000)      

 151 15809        oracle                         oracle@oraprod (q001)      

 137 16329        oracle                         sqlplus@oraprod (TNS V1-V3)    

 134 16380        BSR                           sqlplus.exe

 18 rows selected. 

$ ps -ef|grep 16380  

 oracle 16436 16236  0 17:30:37 pts/0     0:00 grep 16380 

oracle 16380     1  0 17:28:32 ?         0:00 oracleORCL  (LOCAL=NO) 

$ kill -9 16380$

 ====================================================================== 

Using Windows ORAKILL Command: 

======================================== 

SQL> select sid,username,program from v$session;     

   SID USERNAME                       PROGRAM

———- ——————————

  1                                ORACLE.EXE     

 2                                ORACLE.EXE   

 3                                ORACLE.EXE   

 4                                ORACLE.EXE     

 5                                ORACLE.EXE       

6                                ORACLE.EXE   

7                                ORACLE.EXE      

8                                ORACLE.EXE       

9    SCOTT              sqlplus.exe       

10    SYS                    sqlplus.exe  

Find out the SPID related to SCOTT Session (Session ID 9): 

SQL> select  s.sid,p.spid,s.osuser,s.program from v$session s, v$process p   where p.addr=s.paddr;       

 SID SPID         OSUSER                         PROGRAM

———- ———— ——————————

  1 1744         SYSTEM                         ORACLE.EXE       

  2 3716         SYSTEM                         ORACLE.EXE      

   3 3644         SYSTEM                         ORACLE.EXE      

   4 3888         SYSTEM                         ORACLE.EXE       

  5 1992         SYSTEM                         ORACLE.EXE      

   6 2092         SYSTEM                         ORACLE.EXE        

   7 2148         SYSTEM                         ORACLE.EXE      

   8 2224         SYSTEM                         ORACLE.EXE      

   9 2720         BSR                                sqlplus.exe       

10 2144        BSR                                sqlplus.exe 

10 rows selected. 

 Kill the  SPID 2720 related to SCOTT SID 9 session using orakill command 

C:\>orakill TEST 2720

 Kill of thread id 2720 in instance TEST successfully signaled.

 C:\> SQL>  select sid,username,program from v$session       

 SID USERNAME                       PROGRAM

———- ——————————

    1                                ORACLE.EXE      

   2                                ORACLE.EXE       

  3                                ORACLE.EXE      

   4                                ORACLE.EXE       

  5                                ORACLE.EXE      

   6                                ORACLE.EXE      

   7                                ORACLE.EXE       

  8                                ORACLE.EXE       

10 SYS                        sqlplus.exe 

9 rows selected 

Note:  Do not kill the sessions at the OS level as per Oracle Recommendation. As per understanding i have given the above approaches

 Reference: 

(1) Should Sessions be Killed in OS or Using Alter System Kill Session? Doc ID: Note:161794.1

               

About the Author Atul Kumar

Leave a Comment:

6 comments
bilal says January 20, 2008

Hi,

Please let me know how to find out using sql query the concurrent program name based in the PID, and the username who has submitted the request

REgds
Bilal

Reply
Geetha M says July 14, 2008

Thank you Subbu for sharing your knowledge,
so in a nut shell,to kill an Oracle session:
1 – Gather session information from Oracle
2 – Kill the session at the OS-level
3 – Kill the session within Oracle using the “alter system kill session” command:
a) UNIX – I always locate the Server PID (SPID) from v$process and issue the UNIX kill -9 command.
b) The Windows command to kill this session would be as follows.
C:\oracle9i\bin>orakill ORCL92 768

Rgds

Reply
nishit patel says July 23, 2008

Thanks Subba for sharing your knowledge.

Reply
akki says January 29, 2009

thanks alot for sharing the info

Reply
Paul says June 22, 2010

Hi,
Good Doc. Thanks.
Do you have any script to find out the objects locked in the database?

Regards.

Reply
Mag6285 says February 14, 2012

Hi can u help me with one of the issue that we have, we sync documents in our KB but the job hangs due to stellentserver.exe.
the workaround what we do it go to task manager and right click stellentserver.ex then click end process.
once above step is executed then the job runs, again in few minutes same happens.

do you have any resolution that we could try, its on Windows 2003 server.

Reply
Add Your Reply

Not found