Sunday, March 30, 2014

How to Kill Oracle sessions

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

No comments: