Oracle Kill Sessions

If your session is killed you will get an ORA-00028 error message

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;

NOTE: Killing sessions requires the ALTER SYSTEM privilege. It is bad practice to grant this privilege widely (due to the many things it allows a user to do).
If you cannot restrict the need to kill sessions to DBAs (really? could this be indicative of a problem you need to address?) then consider writing a stored procedure to allow killing of a restricted subset of sessions. Also consider logging this activity.

Killing Long Running Sessions

TODO

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