荔园在线

荔园之美,在春之萌芽,在夏之绽放,在秋之收获,在冬之沉淀

[回到开始] [上一篇][下一篇]


发信人: 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软件 网络书店