Oracle | Database Killing Sessions

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;