Tuesday, March 14, 2006

Would like to kill some sessions

How? and How?
First identify the offending session as follows:
SELECT s.sid,
s.serial#,
s.osuser,
s.program
FROM v$session s;

SID SERIAL# OSUSER PROGRAM
---------- ---------- ------------------------------ ---------------
1 1 SYSTEM ORACLE.EXE
2 1 SYSTEM ORACLE.EXE
3 1 SYSTEM ORACLE.EXE
20 60 SYSTEM DBSNMP.EXE
43 11215 USER1 SQLPLUSW.EXE
33 5337 USER2 SQLPLUSW.EXE

The SID and SERIAL# values of the relevant session can then be substituted
into the following statement:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

example
SQL> ALTER SYSTEM KILL SESSION '33,5337';



However, Unix or windows OS , we want to kill from processes

SELECT s.sid,
p.spid,
s.osuser,
s.program
FROM v$process p,
v$session s
WHERE p.addr = s.paddr;

SID SPID OSUSER PROGRAM
---------- --------- ------------------------------ ---------------
1 310 SYSTEM ORACLE.EXE
2 300 SYSTEM ORACLE.EXE
3 309 SYSTEM ORACLE.EXE
20 412 SYSTEM DBSNMP.EXE
43 410 USER1 SQLPLUSW.EXE
33 364 USER2 SQLPLUSW.EXE

if use Windows

C:> orakill ORACLE_SID spid
example
C:> orakill ORACLE_SID 364

if use Unix
% kill -9 spid
example
% kill -9 364


No comments: