Oracle Killing Sessions Version 10.2

kill oracle sessions scrit (with sqlplus)

select s.sid, s.serial#, s.username, spid, 
osuser, s. program, schemaname 
from v$session s, v$process p 
where machine like '%ta%' and s.program like '%%' and s.paddr = p.addr
/

select 'alter system kill session ''' 
|| s.sid || ',' || s.serial# || '''; -- ' 
|| s.machine || ',' || s.program  
from v$session s, v$process p 
where machine like '%%' and s.program like '%%' and s.paddr = p.addr
/

---

Oracle Killing Sessions
Version 10.2

UNIX
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 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
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 '< sid>,';
conn / as sysdba

GRANT alter system TO aqadmin;

conn aqadmin/aqadmin

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

SQL> ALTER SYSTEM KILL SESSION '9,177' 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 Demo Procedure
An infinite loop for testing CREATE OR REPLACE PROCEDURE infinite_loop IS

BEGIN
LOOP
NULL;
END LOOP;
END infinite_loop;
/

SQL> exec infinite_loop