Oracle kill session相關問題（下）

bitifi發表於2016-11-05
OracleSession

 

Oracle kill session相關問題（下）

 

本篇接著上篇講  Oracle kill session相關問題（上）：http://blog.itpub.net/26736162/viewspace-2121019/

 

一.1.1.1  授予普通使用者殺自己session的許可權

若沒有dba角色或者沒有alter system許可權的話，業務使用者就不能自己殺自己會話了，這樣一來，只能dba來介入了，其實這個功能我們可以通過如下的SQL指令碼來完成。

-------  user replace XXXXXX

-------   普通使用者可以kill自己的session 授予應用使用者殺自己session的許可權

create or replace view vw_myownersession_lhr

as

select * from v$session where username = USER;

 

create or replace public synonym syn_myownersession_lhr for sys.vw_myownersession_lhr;

 

 

create or replace procedure pro_kill_myown_session_lhr( p_sid in varchar2,p_serial# in varchar2)

is

    cursor_name     pls_integer default dbms_sql.open_cursor;

    ignore          pls_integer;

BEGIN

    select count(*) into ignore

      from v$session

     where username = USER

       and sid = p_sid

       and serial# = p_serial# ;

 

    if ( ignore = 1 )

    then

        dbms_sql.parse(cursor_name,'alter system disconnect session '''||p_sid||','||p_serial#||''' immediate',dbms_sql.native);

        ignore := dbms_sql.execute(cursor_name);

    else

        raise_application_error( -20001,'You do not own session ''' ||p_sid || ',' || p_serial# ||'''' );

    end if;

END pro_kill_myown_session_lhr;

/

 

create or replace public synonym pro_kill_session_lhr for sys.pro_kill_myown_session_lhr;

 

grant select on syn_myownersession_lhr to XXXXXX;

grant execute on pro_kill_session_lhr to XXXXXX;

 

SELECT USERENV('SID') FROM DUAL;

select sid,serial#,paddr,status from syn_myownersession_lhr where sid=19;

exec pro_kill_session_lhr(19,15);

 

一.1.1.2  ALTER SYSTEM DISCONNECT SESSION 說明

Alter system disconnect session 是一個可選的kill session 的方法。 與kill session 命令不同，disconnect session 命令會kill 掉 dedicated server process, 該命令等同於在作業系統級別kill 掉server process。

 

具體語法如下：

SQL> ALTER SYSTEM DISCONNECT SESSION'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION'sid,serial#' IMMEDIATE;

 

POST_TRANSACTION 選項會等待事務完成之後在斷開連線。

IMMEDIATE 選項會立即斷開連線，然後事務會進行recover操作。

 

這2個選項也可以一起使用，但是必須指定其中一個，否則就會報錯：

SQL> alter system disconnect session'30,7';

alter system disconnect session '30,7'

                                     *

ERROR at line 1:

ORA-02000: missing POST_TRANSACTION orIMMEDIATE keyword

SQL>

 

SQL> alter system disconnect session'15,12' post_transaction immediate;

System altered.

     

使用alter system disconnectsession 命令就不需要切換到系統來kill session，也從而減少了kill 錯程式的機率。

 

一.1.1.3  PMON 清理間隔

PMON程式負責處理異常結束程式相關資源的釋放。PMON週期性地被喚醒，可以對"_PKT_PMON_INTERVAL"這個隱藏引數來進行修改，預設為50秒。也可以通過查詢出程式的PID，然後在oradebug中，執行命令oradebug wakeup orapid(oracle程式的PID，不是OS的PID）來手動喚醒PMON程式。可以用alter session set events '100246 trace name conext forever,level 4'來檢視PMON的相關操作。

SYS@LHRDGZK1> SELECT a.INDX,

  2         a.KSPPINM NAME,

  3         a.KSPPDESC,

  4         b.KSPPSTVL

  5  FROM   x$ksppi  a,

  6         x$ksppcv b

  7  WHERE  a.INDX = b.INDX

  8  and lower(a.KSPPINM) like  lower('%?meter%');

Enter value for parameter: _PKT_PMON_INTERVAL

old   8: and lower(a.KSPPINM) like  lower('%?meter%')

new   8: and lower(a.KSPPINM) like  lower('%_PKT_PMON_INTERVAL%')

 

INDX NAME                   KSPPDESC                             KSPPSTVL

----- ---------------------- ------------------------------------ --------------------

   61 _pkt_pmon_interval     PMON process clean-up interval (cs)  50

 

 

alter system set "_PKT_PMON_INTERVAL"=5;

 

一.1.1  MOS上的一些資料

wpsE3B2.tmpwpsE3B3.tmpwpsE3B4.tmp

wpsE3B5.tmp

wpsE3B6.tmpwpsE3B7.tmpwpsE3C8.tmpwpsE3C9.tmp

wpsE3CA.tmpwpsE3CB.tmpwpsE3CC.tmpwpsE3CD.tmp

於是metalink和google到以下一些資料：

Removing Sessions in Killed Status on Unix [ID 274216.1]

ALTER SYSTEM KILL Session Marked for Killed Forever [ID 1020720.102]

KILLING INACTIVE SESSIONS DOES NOT REMOVE SESSION ROW FROM V$SESSION [ID 1041427.6]

ALTER SYSTEM KILL SESSION does not Release Locks Killing a Thread on Windows NT [ID 100859.1]

 

http://blog.csdn.net/tianlesoftware/article/details/7417058

http://www.eygle.com/faq/Kill_Session.htm

 

 

---比較有用的

How To Find The Process Identifier (pid, spid) After The Corresponding Session Is Killed? [ID 387077.1]

HOW TO HAVE ORACLE CLEAN-UP OLD USER INFO AFTER KILLING SESSION UNDER MTS Note:1023442.6

 

 

 

---------------------------------------------------------------------------------------------------------------------

 

 

第二章 實驗部分

二.1  實驗環境介紹

專案

primary db

db 型別

單例項

db version

11.2.0.3.0

db 儲存

ASM

主機IP地址/hosts配置

192.168.59.129

OS版本及kernel版本

rhel 6.5

 

 

二.2  實驗內容

實驗序號

實驗內容

1

設定使用者profile的idle_time 引數

2

kill session的時候加immediate和不加的區別

3

授予普通使用者kill自己使用者的許可權

4

KILLED狀態的會話如何找到相關的SPID

 

 

二.3  實驗過程

 

二.3.1  設定使用者profile的idle_time 引數

可以參考：

http://blog.csdn.net/leshami/article/details/9184917

http://blog.csdn.net/tianlesoftware/article/details/6238279

 

設定resource_limit 為true，該引數預設為false。

[oracle@orcltest ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 26 17:55:53 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> show parameter RESOURCE_LIMIT

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

resource_limit                       boolean     FALSE

SQL> alter system set resource_limit=true;

 

System altered.

 

 

為業務使用者建立profile檔案。然後把該配置檔案賦給業務使用者。

 

SQL> create profile pro_lhr limit idle_time 1;

 

Profile created.

 

SQL> alter user lhr profile pro_lhr;

 

User altered.

 

SQL>

 

SQL> set line 9999 pagesize 9999

SQL> select * from dba_profiles where profile='PRO_LHR';

 

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

------------------------------ -------------------------------- -------- ----------------------------------------

PRO_LHR                        COMPOSITE_LIMIT                  KERNEL   DEFAULT

PRO_LHR                        SESSIONS_PER_USER                KERNEL   DEFAULT

PRO_LHR                        CPU_PER_SESSION                  KERNEL   DEFAULT

PRO_LHR                        CPU_PER_CALL                     KERNEL   DEFAULT

PRO_LHR                        LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT

PRO_LHR                        LOGICAL_READS_PER_CALL           KERNEL   DEFAULT

PRO_LHR                        IDLE_TIME                        KERNEL   1

PRO_LHR                        CONNECT_TIME                     KERNEL   DEFAULT

PRO_LHR                        PRIVATE_SGA                      KERNEL   DEFAULT

PRO_LHR                        FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT

PRO_LHR                        PASSWORD_LIFE_TIME               PASSWORD DEFAULT

PRO_LHR                        PASSWORD_REUSE_TIME              PASSWORD DEFAULT

PRO_LHR                        PASSWORD_REUSE_MAX               PASSWORD DEFAULT

PRO_LHR                        PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT

PRO_LHR                        PASSWORD_LOCK_TIME               PASSWORD DEFAULT

PRO_LHR                        PASSWORD_GRACE_TIME              PASSWORD DEFAULT

 

16 rows selected.

 

SQL>

 

啟動一個會話，等待一分鐘

D:\Users\xiaomaimiao>sqlplus lhr/lhr@192.168.59.129/oratest

 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 26 18:03:09 2016

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

LHR@192.168.59.129/oratest> set time on

18:03:12 LHR@192.168.59.129/oratest> SELECT a.SID,

18:03:37   2         b.SERIAL# ,

18:03:37   3         c.SPID,

18:03:37   4         b.status

18:03:37   5  FROM   v$mystat  a,

18:03:37   6         v$session b ,

18:03:37   7         v$process c

18:03:37   8  WHERE  a.SID = b.SID

18:03:37   9  and b.PADDR=c.ADDR

18:03:37  10  AND    rownum = 1;

 

       SID    SERIAL# SPID                     STATUS

---------- ---------- ------------------------ --------

       19          9 14689                    ACTIVE

 

 

1分鐘後在其它會話視窗查詢：

SQL> SELECT b.SID,

  2         b.SERIAL# ,

  3         c.SPID,

  4         b.status

  5  FROM   v$session b ,

  6         v$process c

  7  WHERE   b.PADDR=c.ADDR

  8  AND    b.sid=19;

 

       SID    SERIAL# SPID                     STATUS

---------- ---------- ------------------------ --------

        19          9 14689                    INACTIVE

 

SQL> /

 

       SID    SERIAL# SPID                     STATUS

---------- ---------- ------------------------ --------

        19          9 14689                    SNIPED

 

SQL>

 

SQL> alter system kill session '19,9' immediate;

 

System altered.

 

SQL> SELECT b.SID,

  2         b.SERIAL# ,

  3         c.SPID,

  4         b.status

  5  FROM   v$session b ,

  6         v$process c

  7  WHERE   b.PADDR=c.ADDR

  8  AND    b.sid=19;

 

no rows selected

 

SQL>

 

 

二.3.2  授予普通使用者殺自己session的許可權

 

建立普通使用者lhrtest，授予resource和connect許可權。

SQL> create user lhrtest identified by lhrtest;

 

User created.

 

SQL> grant resource ,connect to lhrtest;

 

Grant succeeded.

 

 

 

使用sys使用者來建立需要的指令碼：

SQL> create or replace view vw_myownersession_lhr

  2  as

  3  select * from v$session where username = USER;

 

View created.

 

SQL> create or replace public synonym syn_myownersession_lhr for sys.vw_myownersession_lhr;

 

Synonym created.

 

SQL> create or replace procedure pro_kill_myown_session_lhr( p_sid in varchar2,p_serial# in varchar2)

  2  is

  3      cursor_name     pls_integer default dbms_sql.open_cursor;

  4      ignore          pls_integer;

  5  BEGIN

  6      select count(*) into ignore

  7        from v$session

  8       where username = USER

  9         and sid = p_sid

10         and serial# = p_serial# ;

11 

12      if ( ignore = 1 )

13      then

14          dbms_sql.parse(cursor_name,'alter system disconnect session '''||p_sid||','||p_serial#||''' immediate',dbms_sql.native);

15          ignore := dbms_sql.execute(cursor_name);

16      else

17          raise_application_error( -20001,'You do not own session ''' ||p_sid || ',' || p_serial# ||'''' );

18      end if;

19  END pro_kill_myown_session_lhr;

20  /

 

Procedure created.

 

SQL> create or replace public synonym pro_kill_session_lhr for sys.pro_kill_myown_session_lhr;

 

Synonym created.

 

SQL> grant select on syn_myownersession_lhr to lhrtest;

 

Grant succeeded.

 

SQL> grant execute on pro_kill_session_lhr to lhrtest;

 

Grant succeeded.

 

SQL>

 

 

用windows客戶端登入一個會話：

D:\Users\xiaomaimiao>sqlplus lhrtest/lhrtest@192.168.59.129/oratest

 

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 26 19:19:42 2016

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

LHRTEST@192.168.59.129/oratest> SELECT USERENV('SID') FROM DUAL;

 

USERENV('SID')

--------------

            19

 

LHRTEST@192.168.59.129/oratest> select sid,serial#,paddr,status from syn_myownersession_lhr where sid=19;

 

       SID    SERIAL# PADDR            STATUS

---------- ---------- ---------------- --------

        19         15 0000000077C9B870 ACTIVE

 

不要關閉19,15視窗，然後重新開一個會話視窗：

 

SQL> conn lhrtest/lhrtest

Connected.

SQL> show user

USER is "LHRTEST"

SQL> select sid,serial#,paddr,status from syn_myownersession_lhr where sid=19;

 

       SID    SERIAL# PADDR            STATUS

---------- ---------- ---------------- --------

        19         15 0000000077C9B870 INACTIVE

 

SQL> alter system kill session '19,15' immediate;

alter system kill session '19,15' immediate

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

 

SQL>  exec pro_kill_session_lhr(19,15);

 

PL/SQL procedure successfully completed.

 

SQL> select sid,serial#,paddr,status from syn_myownersession_lhr where sid=19;

 

no rows selected

 

SQL>

 

可以看到普通使用者也可以殺掉自己使用者的會話了。

 

 

二.3.3  kill session的時候加immediate和不加immediate的區別

 

kill session 的測試：

SQL> set line 9999

SQL> col sessionid format a20

SQL> col sessionid_killed format a20

SQL> col kill_session format a60

SQL>

SQL> SELECT a.INST_ID,

  2         a.SID || ',' || a.SERIAL# || ',' ||

  3         (select spid

  4            from gv$process b

  5           where b.INST_ID = a.INST_ID

  6             and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

  7          ) sessionid,

  8         a.PADDR,

  9         a.STATUS,

10         a.PROGRAM,

11         a.server,

12         'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

13    FROM gv$session a

14   WHERE a.type != 'BACKGROUND';

 

   INST_ID SESSIONID            PADDR            STATUS   PROGRAM                                  SERVER    KILL_SESSION

---------- -------------------- ---------------- -------- ---------------------------------------- --------- ------------------------------------------------------------

         1 9,169,14901          0000000077C9B870 INACTIVE sqlplus.exe                              DEDICATED alter system disconnect session '9,169' immediate;

         1 20,9,14891           0000000077CA5F50 INACTIVE plsqldev32.exe                           DEDICATED alter system disconnect session '20,9' immediate;

         1 23,35,14885          0000000077C99710 INACTIVE sqlplus.exe                              DEDICATED alter system disconnect session '23,35' immediate;

         1 125,7,14873          0000000077C98660 ACTIVE   sqlplus@orcltest (TNS V1-V3)             DEDICATED alter system disconnect session '125,7' immediate;

         1 145,23,14651         0000000077CA9160 INACTIVE plsqldev32.exe                           DEDICATED alter system disconnect session '145,23' immediate;

 

SQL> alter system kill session '9,169';

 

System altered.

 

SQL> alter system kill session '20,9';

 

System altered.

 

SQL> set line 9999

SQL> col sessionid format a20

SQL> col sessionid_killed format a20

SQL> col kill_session format a60

SQL>

SQL> SELECT a.INST_ID,

  2         a.SID || ',' || a.SERIAL# || ',' ||

  3         (select spid

  4            from gv$process b

  5           where b.INST_ID = a.INST_ID

  6             and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

  7          ) sessionid,

  8         a.PADDR,

  9         a.STATUS,

10         a.PROGRAM,

11         a.server,

12         'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

13    FROM gv$session a

14   WHERE a.type != 'BACKGROUND';

 

   INST_ID SESSIONID            PADDR            STATUS   PROGRAM                                  SERVER    KILL_SESSION

---------- -------------------- ---------------- -------- ---------------------------------------- --------- ------------------------------------------------------------

         1 9,169,14901          0000000077D2DCF8 KILLED   sqlplus.exe                              PSEUDO    alter system disconnect session '9,169' immediate;

         1 20,9,14891           0000000077D2DCF8 KILLED   plsqldev32.exe                           PSEUDO    alter system disconnect session '20,9' immediate;

         1 23,35,14885          0000000077C99710 INACTIVE sqlplus.exe                              DEDICATED alter system disconnect session '23,35' immediate;

         1 125,7,14873          0000000077C98660 ACTIVE   sqlplus@orcltest (TNS V1-V3)             DEDICATED alter system disconnect session '125,7' immediate;

         1 145,23,14651         0000000077CA9160 INACTIVE plsqldev32.exe                           DEDICATED alter system disconnect session '145,23' immediate;

 

SQL>

 

可以看到，殺掉的2個會話在v$session中都可以查到，只是①其status變為了KILLED，②server列變為了PSEUDO,③paddr列都變為了一樣的了，因此這個時候如若還使用paddr列來關聯process列必然查詢不到spid號了，這個時候可以使用前邊我們介紹的幾種SQL來查詢會話的spid，然後用kill -9殺掉會話。

我們用kill -9殺掉會話,等待後臺PMON來自動清理程式，若是PMON很慢，我們可以手動來用oradebug wakeup 2來喚醒PMON程式，還可以設定PMON的清理間隔alter system set "_PKT_PMON_INTERVAL"=5;：

 

[oracle@orcltest ~]$ ps -ef|grep 14901

oracle   14901     1  0 19:51 ?        00:00:00 oracleoratest (LOCAL=NO)

oracle   14959 14625  0 20:17 pts/8    00:00:00 grep 14901

[oracle@orcltest ~]$ kill -9 14901

[oracle@orcltest ~]$ ps -ef|grep 14901

oracle   14961 14625  0 20:17 pts/8    00:00:00 grep 14901

[oracle@orcltest ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 26 20:17:54 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

 

SQL> set line 9999

SQL> col sessionid format a20

SQL> col sessionid_killed format a20

SQL> col kill_session format a60

SQL>

SQL> SELECT a.INST_ID,

  2         a.SID || ',' || a.SERIAL# || ',' ||

  3         (select spid

  4            from gv$process b

  5           where b.INST_ID = a.INST_ID

  6             and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

  7          ) sessionid,

  8         a.PADDR,

  9         a.STATUS,

10         a.PROGRAM,

11         a.server,

12         'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

13    FROM gv$session a

14   WHERE a.type != 'BACKGROUND';

 

   INST_ID SESSIONID            PADDR            STATUS   PROGRAM                                          SERVER    KILL_SESSION

---------- -------------------- ---------------- -------- ------------------------------------------------ ----------- ---------------------------------------------------------

         1 9,169,14901          0000000077D2DCF8 KILLED   sqlplus.exe                                      PSEUDO    alter system disconnect session '9,169' immediate;

         1 20,9,14891           0000000077D2DCF8 KILLED   plsqldev32.exe                                   PSEUDO    alter system disconnect session '20,9' immediate;

         1 23,35,14885          0000000077C99710 INACTIVE sqlplus.exe                                      DEDICATED alter system disconnect session '23,35' immediate;

         1 125,9,14964          0000000077C98660 ACTIVE   sqlplus@orcltest (TNS V1-V3)                     DEDICATED alter system disconnect session '125,9' immediate;

 

 

SQL> select INST_ID, spid, program,A.PNAME,A.PID

  2    from gv$process a

  3  where a.PNAME='PMON';

 

   INST_ID SPID       PROGRAM                                  PNAME        PID

---------- ---------- ---------------------------------------- ----- ----------

         1 13955      oracle@orcltest (PMON)                   PMON           2

 

SQL> oradebug wakeup 2

Statement processed.

SQL>

SQL> SET LINESIZE 180

SQL> COLUMN spid FORMAT A10

SQL> COLUMN username FORMAT A10

SQL> COLUMN program FORMAT A40

SQL> SELECT s.inst_id,

  2         s.sid,

  3         s.serial#,

  4         p.spid,

  5         s.username,

  6         s.program,

  7         s.paddr,

  8         s.STATUS,

  9   s.server

10    FROM gv$session s

11    left outer JOIN gv$process p

12      ON p.addr = s.paddr

13     AND p.inst_id = s.inst_id

14   WHERE s.type != 'BACKGROUND';

 

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM                                  PADDR            STATUS   SERVER

---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------------- -------- ---------

         1        125          9 14964      SYS        sqlplus@orcltest (TNS V1-V3)             0000000077C98660 ACTIVE   DEDICATED

         1         23         35 14885      SYS        sqlplus.exe                              0000000077C99710 INACTIVE DEDICATED

         1         20         11 14966      SYS        plsqldev32.exe                           0000000077C9B870 INACTIVE DEDICATED

         1          9        177 14968      SYS        plsqldev32.exe                           0000000077CA5F50 INACTIVE DEDICATED

 

SQL>

 

kill session immediate的測試：

SQL> set line 9999

SQL> col sessionid format a20

SQL> col sessionid_killed format a20

SQL> col kill_session format a60

SQL>

SQL> SELECT a.INST_ID,

  2         a.SID || ',' || a.SERIAL# || ',' ||

  3         (select spid

  4            from gv$process b

  5           where b.INST_ID = a.INST_ID

  6             and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

  7          ) sessionid,

  8         a.PADDR,

  9         a.STATUS,

10         a.PROGRAM,

11         a.server,

12         'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

13    FROM gv$session a

14   WHERE a.type != 'BACKGROUND';

 

   INST_ID SESSIONID            PADDR            STATUS   PROGRAM                                  SERVER    KILL_SESSION

---------- -------------------- ---------------- -------- ---------------------------------------- --------- ------------------------------------------------------------

         1 9,169,14901          0000000077D2DCF8 KILLED   sqlplus.exe                              PSEUDO    alter system disconnect session '9,169' immediate;

         1 20,9,14891           0000000077D2DCF8 KILLED   plsqldev32.exe                           PSEUDO    alter system disconnect session '20,9' immediate;

         1 23,35,14885          0000000077C99710 INACTIVE sqlplus.exe                              DEDICATED alter system disconnect session '23,35' immediate;

         1 125,7,14873          0000000077C98660 ACTIVE   sqlplus@orcltest (TNS V1-V3)             DEDICATED alter system disconnect session '125,7' immediate;

         1 145,23,14651         0000000077CA9160 INACTIVE plsqldev32.exe                           DEDICATED alter system disconnect session '145,23' immediate;

 

SQL> alter system kill session '145,23' immediate;

 

System altered.

 

SQL> set line 9999

SQL> col sessionid format a20

SQL> col sessionid_killed format a20

SQL> col kill_session format a60

SQL>

SQL> SELECT a.INST_ID,

  2         a.SID || ',' || a.SERIAL# || ',' ||

  3         (select spid

  4            from gv$process b

  5           where b.INST_ID = a.INST_ID

  6             and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

  7          ) sessionid,

  8         a.PADDR,

  9         a.STATUS,

10         a.PROGRAM,

11         a.server,

12         'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

13    FROM gv$session a

14   WHERE a.type != 'BACKGROUND';

 

   INST_ID SESSIONID            PADDR            STATUS   PROGRAM                                  SERVER    KILL_SESSION

---------- -------------------- ---------------- -------- ---------------------------------------- --------- ------------------------------------------------------------

         1 9,169,14901          0000000077D2DCF8 KILLED   sqlplus.exe                              PSEUDO    alter system disconnect session '9,169' immediate;

         1 20,9,14891           0000000077D2DCF8 KILLED   plsqldev32.exe                           PSEUDO    alter system disconnect session '20,9' immediate;

         1 23,35,14885          0000000077C99710 INACTIVE sqlplus.exe                              DEDICATED alter system disconnect session '23,35' immediate;

         1 125,7,14873          0000000077C98660 ACTIVE   sqlplus@orcltest (TNS V1-V3)             DEDICATED alter system disconnect session '125,7' immediate;

 

SQL>

可以看到若加上immediate的話會話沒有事務的情況下會立即釋放，且v$session檢視也被清理了。

 

---------------------------------------------------------------------------------------------------------------------

 

 

 

 About Me

..........................................................................................................................................................................................................

本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

本文在ITpub（http://blog.itpub.net/26736162）和部落格園(http://www.cnblogs.com/lhrbest)有同步更新

本文地址：http://blog.itpub.net/26736162/viewspace-2121019/和http://blog.itpub.net/26736162/viewspace-2121020/

本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取碼：ed9b）

小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/

聯絡我請加QQ好友(642808185)，註明新增緣由

於 2016-06-15 10:00~ 2016-06-26 19:00 在中行完成

【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】

..........................................................................................................................................................................................................

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22207394/viewspace-2121115/，如需轉載，請註明出處，否則將追究法律責任。

相關文章