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