Oracle Kill Sessions

Oracle Enterprise Manager

Targets - Databases - MyDB - Performance - Search Sessions

Examples
To search for all SQL Developer sessions Filter by Module "SQL Developer"

SQL*Plus

Identify sid and serial# of session to kill...

SET PAGESIZE 40

SET LINESIZE 180


COLUMN sid       FORMAT 999 WRAP

COLUMN serial#   FORMAT 99999999 WRAP

COLUMN terminal  FORMAT a10 WRAP

COLUMN username  FORMAT a7 WRAP

COLUMN status    FORMAT a8 WRAP

COLUMN osuser    FORMAT a7 WRAP

COLUMN process   FORMAT a7 WRAP

COLUMN machine   FORMAT a20 WRAP

COLUMN terminal  FORMAT a10 WRAP


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-RR HH24:MI:SS';


SELECT sid,

       serial#,

       username,

       status,

       osuser,

       process,

       machine,

       terminal,

       program,

       logon_time

  FROM v$session;

Kill session...

ALTER SYSTEM KILL SESSION '&sid, &serial';

ALTER SYSTEM DISCONNECT SESSION '&sid, &serial' IMMEDIATE;

Orphan OS Processes

When an Oracle process is Killed, the PMON process co-ordinates any cleanup activities within the database. Usually, the related OS processes also die, but it is possible for an orphan OS process to be left running.

Check with...

SELECT *

  FROM v$process p

 WHERE NOT EXISTS (SELECT 1

                     FROM v$session s

                    WHERE s.paddr = p.addr);

Processes with a PNAME of S000 or D000 or with a PROGRAM of PSEUDO can generally be ignored. The OS process start date will generally match the Oracle instance start date.

On UNIX/Linux, use the spid returned by the query above to check the os process using...

ps -ef | grep spid

If appropriate, kill the process using...

kill spid

or, if the process does not die...

kill -9 spid

On Windows use the sid and thread from the sa,e spid column to kill the process using orakill...

orakill sid thread

Bibliography