| Overview |
Killing sessions is a critically important DBA skill.
If you are alerted to a session that is misbehaving in some manner, might be an attack, might be a performance impacting issue, you must be prepared (no time to look it up with google) to kill the suspect session.
In a container database you can kill PDB sessions in both the PDB to which the session is connected as well as from CDB$ROOT (con_id = 1). |
| |
| *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;
INSTANCE_NAME
----------------
orabasexix
col program format a20
col osuser format a30
col schemaname format a30
SELECT p.inst_id, p.spid, s.osuser, s.program, s.schemaname, p.con_id
FROM gv$process p, gv$session s
WHERE p.inst_id = s.inst_id
AND p.addr = s.paddr
AND s.program NOT LIKE 'ORACLE%';
INST_ID SPID OSUSER PROGRAM SCHEMANAME CON_ID
-------- ------ ---------------- ------------ ----------- -------
1 25012 PERRITO5\oracle sqlplus.exe UWCLASS 3
1 16532 PERRITO5\oracle sqlplus.exe SYS 1
host
C:\u01\orahome19\WINDOWS.X64_193000_db_home\bin> orakill orabasexix 25012
Kill of thread id 25012 in instance orabasexix successfully signalled.
-- in the UWCLASS sqlplus session in CON_ID 3
-- try a simple query and verify the session is dead
SQL> SELECT * FROM dual;
SELECT * FROM dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 25012
Session ID: 145 Serial number: 4020
ERROR:
ORA-03114: not connected to ORACLE |
| |
| 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, con_id
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, con_id
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;
set trim on
set trimspool on
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');
'EXECUTEKILL_SESSION('||CHR(39)||SID||CHR(39)||','||CHR(39)||SERIAL#||CHR(39)||');'
------------------------------------------------------------------------------------
execute kill_session('28','11700');
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, program, (SYSDATE-logon_time)*24 duration
FROM gv$session
WHERE username IS NOT NULL
ORDER BY program;
SQLSTMT PROGRAM DURATION
-------------------------------------------------- -----------
----------
alter system kill session '28,11700,@1' immediate; sqlplus.exe .955277778 |
| |
| Conclusion |
Learning how to kill sessions is not an optional skill. And you cannot and should not depend on a third-party tool, not even OEM, to do it for you.
When you need to kill a session to stop an attack you need to do it in the database and the operating system. Be sure you have the experience necessary to do so quickly and decisively. |