CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

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 <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 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 <schema_name>;

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

ALTER SYSTEM KILL SESSION '<sid>,<serial#>';
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
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [82 users online]    © 2010 psoug.org