Oracle Killing Sessions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Container Database Note Remember that in a container database ... you will have to be in the container in which the session is running to effect a kill. Logged into pdb$root sessions in other PDBs will not be visible.
 
*NIX
Killing sessions in the UNIX environment ps -ef | grep ora to find Oracle processes. Be sure to get the process id of the session you are trying to kill

kill -1 <process_id>
 or
kill -9 <process_id>
kill -9 5745
All in one kill ps -ef | grep pmon_$ORACLE_SID | awk '{print $2}' | xargs kill -9
 
Windows
Killing sessions in the Windows environment with ORAKILL orakill <instance_name> <spid>
SELECT instance_name
FROM gv$instance;

col program format a30

SELECT inst_id, spid, osuser, s.program, schemaname
FROM gv$process p, gv$session s
WHERE p.addr = s.paddr;

c:\oracle\product\ora102\bin> orakill orabase spid
 
All Operating Systems
Killing sessions from inside the database -- to kill sessions within the database requires the ALTER SYSTEM privilege and the sid and serial# of the session to be killed

GRANT alter system TO <schema_name>;

SELECT sid, serial#, username, schemaname, osuser
FROM gv$session
WHERE username = <user_name>;

ALTER SYSTEM KILL SESSION '<sid>,<serial#>,<@instance_number>' [< IMMEDIATE | NOREPLAY>];
conn / as sysdba

GRANT alter system TO aqadmin;

conn aqadmin/aqadmin

SELECT inst_id, sid, serial#, username, schemaname, osuser
FROM gv$session
WHERE username = 'AQUSER';

ALTER SYSTEM KILL SESSION '9,177, @1' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION '<sid>,<serial#>,<@instance_number>' [< IMMEDIATE | NOREPLAY>];
SELECT inst_id, sid, serial#, username, program
FROM gv$session
WHERE username = 'UWCLASS';

ALTER SYSTEM DISCONNECT SESSION '141,12481,@3' IMMEDIATE;
 
Kill All Sessions
Kill All Instance Sessions conn / as sysdba

set heading off
set termout off
set verify off
set echo off
set feedback off

ALTER SYSTEM enable restricted session;

ALTER SYSTEM checkpoint global;

spool kill_all.sql

SELECT 'execute kill_session('|| chr(39) || sid || chr(39) || ',' || chr(39) || serial# || chr(39) || ');'
FROM gv_$session
WHERE (username IS NOT NULL OR username <> 'SYS');

spool off

@kill_all
 
Session Kill Demos
An infinite loop for testing CREATE OR REPLACE PROCEDURE infinite_loop IS
BEGIN
  LOOP
    NULL;
  END LOOP;
END infinite_loop;
/

SQL> exec infinite_loop
Script to create kill statements col machine format a20
col program format a20
col sqlstmt format a50
set linesize 141

SELECT 'alter system kill session ''' || sid || ',' || serial# || ',@' || inst_id || ''' immediate;' sqlstmt, machine,program, (SYSDATE-logon_time)*24 duration
FROM gv$session
WHERE username IS NOT NULL
ORDER BY program;

Related Topics
DBMS_SESSION
SESSIONS

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved