RAC query

http://www.morganslibrary.org/reference/rac.html

http://www.morganslibrary.org/reference/kill_session.html

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
or
kill -9

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

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 ;

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

ALTER SYSTEM KILL SESSION ',,<@instance_number>' [IMMEDIATE];

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 ',,<@instance_number>' [IMMEDIATE];

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;