kill sessions

[gatetec]

I found a lot of sessions are hung. I killed the processes on AIX, but the Oracle sessions seemed to be running still. How do you kill sessions to make sure that none of hung processes/jobs is running?

thx much

 

[SantaMufasa]

Gatetec,

Instead of killing a process from the operating-system level, I kill unwanted sessions from withing Oracle. You can use this syntax:

ALTER SYSTEM KILL SESSION '<session id>,<serial #>';

To find the values of <session id> and <serial #>, I use my "logins.sql" script:

set linesize 200
set echo off
set feedback on
col oruser format a20 heading "Oracle Username"
col osuser format a12 heading "O/S Username"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a20
col computer heading "Computer/Terminal" format a21
col program format a50 heading "Program"
col process format 999999 heading "OS|Process|ID"
select     username oruser
   ,osuser osuser
   ,machine computer
   ,''||s.sid||','||s.serial#||'' ss
   ,status
   ,process
   ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
   ,program
from       gv$session s
order by time
/

Sample output from running "logins.sql" appears below. (Since the output is wider than a normal screen, I've divided the output into two vertical pieces for ease of reading.)

SQL> @logins

Oracle Username      O/S Username Computer/Terminal     SID/Ser#     STATUS  
-------------------- ------------ --------------------- ------------ --------
                     oracle       alpine                '1,1'        ACTIVE  
                     oracle       alpine                '2,1'        ACTIVE  
                     oracle       alpine                '3,1'        ACTIVE  
                     oracle       alpine                '4,1'        ACTIVE  
                     oracle       alpine                '5,1'        ACTIVE  
                     oracle       alpine                '6,1'        ACTIVE  
jdoe                 oracle       PC2556                '210,2939'   ACTIVE  
jdoe                 oracle       PC2556                '133,4857'   ACTIVE  
jdoe                 oracle       PC2556                '243,4556'   ACTIVE  
jdoe                 oracle       PC2556                '199,20087'  ACTIVE  


OS
Process
ID           Logon Date/Time      Program
------------ -------------------- --------------------------
2885         2007/11/04 22:22:30  oracle@alpine (PMON)
2887         2007/11/04 22:22:31  oracle@alpine (DBW0)
2889         2007/11/04 22:22:31  oracle@alpine (LGWR)
2891         2007/11/04 22:22:31  oracle@alpine (CKPT)
2893         2007/11/04 22:22:31  oracle@alpine (SMON)
2895         2007/11/04 22:22:31  oracle@alpine (RECO)
21722        2007/11/19 12:02:20  sqlplus@alpine (TNS V1-V3)
22146        2007/11/19 12:14:11  sqlplus@alpine (TNS V1-V3)
22144        2007/11/19 12:14:11  sqlplus@alpine (TNS V1-V3)
22148        2007/11/19 12:14:11  sqlplus@alpine (TNS V1-V3)

Notice in the above listing that the "SID" and "Ser#" appear in quotes, separated by a comma. This is the exact format necessary to copy and paste into the "ALTER SYSTEM KILL SESSION..." command.

Also notice that the first six processes are Oracle's standard background processes, PMON, DBW0, LGWR, CKPT, SMON, and RECO. The other processes are end-user logins that, in this case, are SQL*Plus sessions by a single user from the same machine, "PC2556".

Let us know if this is useful for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]