Kill Oracle 12cR2 Database Killing Sessions

Oracle Killing Sessions Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

The Library is currently in the process of being upgraded from Oracle Database Version to 12.2.0.1 and version 18.0 is going to be available soon. 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 and beyond.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.

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;