荔园在线
荔园之美,在春之萌芽,在夏之绽放,在秋之收获,在冬之沉淀
[回到开始]
[上一篇][下一篇]
发信人: Kun (Diao), 信区: Database
标 题: :[转载]如何通过sql语句kill一个
发信站: 荔园晨风BBS站 (Thu Dec 6 18:58:21 2001), 转信
1、通常kill session是由DBA手动处理的,要非常谨慎
2、BACKGROUD sessions不能kill,否则会引起不可预知的错误
3、ACTIVE sessions不能kill
4、即使session是inactive的,也不表示就可以随便kill
SQL> select sid,serial#, username, status, type
2 FROM v$session;
SID SERIAL# USERNAME STATUS TYPE
---------- ---------- ------------------------------ -------- ----------
1 1 ACTIVE BACKGROUND
2 1 ACTIVE BACKGROUND
3 1 ACTIVE BACKGROUND
4 1 ACTIVE BACKGROUND
5 1 ACTIVE BACKGROUND
6 1 ACTIVE BACKGROUND
7 21 KNUT INACTIVE USER
8 33 SYSTEM ACTIVE USER
8 rows selected.
如果你一定要的话,可以试试这样(kill 所有inactive sessions,太霸道了吧!
):
SQL> connect system/manager
Connected.
SQL> select sid,serial#, username, status, type
2 from v$session;
SID SERIAL# USERNAME STATUS TYPE
---------- ---------- ------------------------------ -------- ----------
1 1 ACTIVE BACKGROUND
2 1 ACTIVE BACKGROUND
3 1 ACTIVE BACKGROUND
4 1 ACTIVE BACKGROUND
5 1 ACTIVE BACKGROUND
6 1 ACTIVE BACKGROUND
7 26 KNUT INACTIVE USER
8 35 SYSTEM ACTIVE USER
9 19 WMS INACTIVE USER
9 rows selected.
SQL> declare
2 cursor cur_sess is
3 select sid, serial#
4 from v$session
5 where status = 'INACTIVE'
6 and type != 'BACKGROUD';
7 w_sid number;
8 w_serial number;
9 begin
10 open cur_sess;
11 loop
12 fetch cur_sess into w_sid,w_serial;
13 if cur_sess%notfound then
14 exit;
15 end if;
16 execute immediate 'alter system kill session '''¦
¦w_sid¦¦','¦¦w_serial¦
¦'''';
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
SQL> select sid,serial#, username, status, type
2 from v$session;
SID SERIAL# USERNAME STATUS TYPE
---------- ---------- ------------------------------ -------- ----------
1 1 ACTIVE BACKGROUND
2 1 ACTIVE BACKGROUND
3 1 ACTIVE BACKGROUND
4 1 ACTIVE BACKGROUND
5 1 ACTIVE BACKGROUND
6 1 ACTIVE BACKGROUND
7 26 KNUT KILLED USER
8 35 SYSTEM ACTIVE USER
9 19 WMS KILLED USER
9 rows selected.
--
※ 来源:·荔园晨风BBS站 bbs.szu.edu.cn·[FROM: 192.168.32.34]
[回到开始]
[上一篇][下一篇]
荔园在线首页 友情链接:深圳大学 深大招生 荔园晨风BBS S-Term软件 网络书店