Question

如何快速的杀掉Oracle的Session

Answer 1

举个例子
通过session id (SID) 找到系统进程号 然后kill 进程
SQL> select spid from v$process where addr=(select paddr from v$session where rownum=1 and sid=(select userenv('SID') from dual));

SPID
------------------------
12135

spid 系统进程号

SQL> ho kill -9 12135
kill 掉这个进程 就完了

SQL> select sysdate from dual;
select sysdate from dual
*
第 1 行出现错误:
ORA-03135: 连接失去联系
进程 ID: 12135
会话 ID: 65 序列号: 19533

Answer 2

需要查询SID和SERIAL#,然后kill掉。
方法1、用sql语句
SHELL> sqlplus /nolog
SQL> conn /as sysdba
SQL> set head off
SQL> spool kill.txt
SQL> select ‘alter system kill session ‘ || ”” || a.SID || ‘,’ || a.SERIAL# || ””||’ ;’ from v$session a where a.STATUS = ‘ACTIVE' and osuser='alex';
SQL> spool off
SQL> exit
SHELL>sed -i ‘/^$/d’ ~/kill.txt
SHELL>sqlplus /nolog
SQL> conn /as sysdba
SQL> @kill.txt
SQL> exit

方法2、用proc
declare cursor mycur is
select b.sid,b.serial#
　　from v$locked_object a,v$session b
　　where a.session_id = b.sid group by b.sid,b.serial#;
begin
for cur in mycur
loop
execute immediate ( ‘alter system kill session ”’||cur.sid || ‘,’|| cur.SERIAL# ||”’ ‘);
end loop;
end;