Oracle Killing Sessions
Version 20c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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 Use 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;

/u01/orahome19/product/dbhome_1/bin>> orakill orabasexxspid
 
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
Built-in Functions
Built-in Packages
DBMS_SESSION
SESSIONS
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx