Database sessions and more ..

Kill Oracle Sessions

A user has fired a sql statement which is stuck and not moving along. As a DBA you need to kill the oracle session. This is what you can do

• USING SQL PLUS               - Find out the SID and SERIAL# of the SESSION

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


Then use the following query to kill the session

          alter system kill session ‘SID, SERIAL#’; 

          This statement does the following--

                      Terminates the session

                       Rolls back transactions

                       Releases all session locks

                       Frees all session resources

             If the session is waiting for some activity that must be completed --for example waiting for a reply from a remote database or rolling back a transaction then Oracle waits for this activity to complete and then kills the session. If the wait lasts for 60 seconds then oracle marks this session to be killed and returns the message that session is marked for kill. 

• USING KILL COMMAND       --Find out the SPID (Server Process ID) of the oracle session. then issue the kill command   

           kill -9 <SPID>

Look at the following queries

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

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

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. 

 alter system kill session ’134,47747′ immediate; 

Using Unix KILL Command:

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

Look at the following note-

Should Sessions be Killed in OS or Using Alter System Kill Session? [ID 161794.1]