關於v$session中LAST_CALL_ET列的理解：http://blog.itpub.net/26736162/viewspace-1762403/

根據v$session中LAST_CALL_ET列的意義我們可以寫出如下的SQL指令碼，沒一個小時清理10個小時沒有響應的會話：

set sqlblanklines on

CREATE OR REPLACE PROCEDURE P_kill_session_LHR AS

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

        -- Created on 2013-06-25 12:05:07 by lhr

        --Changed on 2015-08-05 12:05:07 by lhr

        -- function：  殺掉10個小時之前的會話 ,告警日誌中會記錄被殺掉的會話資訊

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

    BEGIN

        -- IF to_char(SYSDATE, 'HH24') >= '20' OR

        --     TO_CHAR(SYSDATE, 'HH24') <= '08' THEN

        FOR cur IN (SELECT A.USERNAME,

                           A.LOGON_TIME,

                           A.STATUS,

                           A.SID,

                           A.SERIAL#,

                           A.MACHINE,

                           A.OSUSER,

                           'ALTER SYSTEM DISCONNECT SESSION ''' || a.SID || ',' ||

                           a.serial# || ''' IMMEDIATE' kill_session

                    FROM   v$session A

                    WHERE  A.STATUS IN ('INACTIVE')

                    AND    A.USERNAME IS NOT NULL

                    AND    A.LAST_CALL_ET >= 60 * 60 * 10) LOOP

            BEGIN

                EXECUTE IMMEDIATE cur.kill_session;

            EXCEPTION

                WHEN OTHERS THEN

                    NULL;

            END;

        END LOOP;

        -- END IF;

    EXCEPTION

        WHEN OTHERS THEN

            NULL;

    END P_kill_session_LHR;

/

BEGIN

    --DBMS_SCHEDULER.drop_job('JOB_P_kill_session_LHR');

    DBMS_SCHEDULER.CREATE_JOB(JOB_NAME        => 'JOB_P_kill_session_LHR',

                              JOB_TYPE        => 'STORED_PROCEDURE',

                              JOB_ACTION      => 'P_kill_session_LHR',

                              repeat_interval => 'FREQ=MINUTELY;INTERVAL=60',

                              ENABLED         => TRUE,

                              START_DATE      => SYSDATE,

                              COMMENTS        => '刪除--每60分鐘檢查一次');

END;

/

SELECT d.job_name,d.job_action,d.run_count FROM dba_scheduler_jobs d WHERE d.job_name='JOB_P_KILL_SESSION_LHR';

SQL> col JOB_ACTION format a20
SQL>  SELECT d.job_name,d.job_action,d.run_count FROM dba_scheduler_jobs d WHERE d.job_name='JOB_P_KILL_SESSION_LHR';

JOB_NAME                       JOB_ACTION            RUN_COUNT
------------------------------ -------------------- ----------
JOB_P_KILL_SESSION_LHR         P_kill_session_LHR            4

執行日誌：

SELECT * FROM dba_scheduler_job_run_details  d WHERE d.job_name='JOB_P_KILL_SESSION_LHR';

1.3.3  oracle死連線(DC)和不活動會話(INACTIVE)

This note explains the difference between a dead connection and an INACTIVE session in v$session.  It also discusses the mechanisms provided to automate the cleanup of each.

這裡解釋死連線和不活動會話的區別，也會討論自動清除的機制。

Difference between INACTIVE sessions and Dead Connections

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

Dead connections and INACTIVE sessions are different issues. Oracle provides separate mechanisms to automate the cleanup of each.

死連線和不活動會話的不同問題，oracle提供了分離的機制去自動清理。

(1) Dead connections:死連線

    These are previously valid connections with the database but the  connection between the client and server processes has terminated   abnormally.

有些之前合法的連線，但是由於客戶端和伺服器程式的異常中斷。

    Examples of a dead connection:

    - A user reboots/turns-off their machine without logging off  or disconnecting from the database.

    - A network problem prevents communication between the client   and the server.

    1.使用者沒有登出就關閉機器。

    2.網路在客戶端和伺服器端終端連線。

    In these cases, the shadow process running on the server and the session in the database may not terminate. To automate the cleanup  of these sessions, you can use the Dead Connection Detection (DCD)   feature of Net8.

   這種情況下，後臺程式跑在伺服器端，而會話在資料庫端不會中斷。

    When DCD is enabled, Net8 (server-side) sends a packet to the client.  If the client is active, the packet is discarded. If the client has  terminated, the server will receive an error and Net8 (server-side)  will end that session.

當死連線啟動，NET8(服務端)會傳送一個包到客戶端。如果客戶端是活動的，這個包就被丟掉。如果客戶端已經被中斷，伺服器端將接收一個錯誤，將會中斷該會話。SQLNET.EXPIRE_TIME.

Refer to Note:151972.1: Dead Connection Detection (DCD) Explained,  for details regarding DCD.

(2) INACTIVE Sessions:

    These are sessions that remain connected to the database with a  status in v$session of INACTIVE.

會話與伺服器端保持連線，但是狀態為inactive.

    Example of an INACTIVE session:

    - A user starts a program/session, then leaves it running and idle for an extended period of time.

    使用者開始一個會話，執行一段時間後，保持相當一段時間的空閒。

    To automate cleanup of INACTIVE sessions you can create a profile  with an appropriate IDLE_TIME setting and assign that profile to  the users.

   自動清理不活動的會話，你可以創一個profile.然後設定恰當的IDLE_TIME,分配給指定使用者。

    Note:159978.1: How To Automate Disconnection of Idle Sessions,  outlines the steps to setup IDLE_TIME for this.

1.3.4  kill session相關內容

kill session 是DBA經常碰到的事情之一。如果kill 掉了不該kill 的session，則具有破壞性，因此盡可能的避免這樣的錯誤發生。同時也應當注意，如果kill 的session屬於Oracle 後臺進程，則容易導致數據庫實例當機。

一、獲得需要kill session的資訊

SET LINESIZE 180

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A40

SELECT s.inst_id,

         s.sid,

         s.serial#,

         p.spid,

         s.username,

         s.program,

         s.paddr,

         s.STATUS

FROM   gv$session s

JOIN gv$process p

ON p.addr = s.paddr

AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND';

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM                                  PADDR            STATUS

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

         1        125          5 14029      SYS        sqlplus@orcltest (TNS V1-V3)             0000000077C98660 INACTIVE

         1          9         15 14274      SYS        sqlplus@orcltest (TNS V1-V3)             0000000077C99710 INACTIVE

         1         17          5 14078      LHR        sqlplus.exe                              0000000077CA5F50 INACTIVE

         1        144         31 14645      SYS        sqlplus@orcltest (TNS V1-V3)             0000000077CA7000 ACTIVE

         1         20          7 14647      SYS        plsqldev32.exe                           0000000077CA80B0 INACTIVE

         1        145         23 14651      SYS        plsqldev32.exe                           0000000077CA9160 INACTIVE

二、使用ALTER SYSTEM KILL SESSION 命令實現

  語法：

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

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

    對於RAC環境下的kill session ,需要搞清楚需要kill 的session 位於哪個節點，可以查詢GV$SESSION檢視獲得。11g殺掉叢集環境下的某個會話：

alter system kill session'1228,42549,@例項號';

例如：alter system kill session '1228, 42549, @2'；

10g下應登入到某個特定的例項才可以。

Kill session 命令實際不會kill session，比如等待遠端資料庫的反應或者回滾事務，那麼session 就不會立即kill其必須等待當前的操作結束才能執行，在這種情況下，session 就會被標記為killed 狀態。

       我們可以在kill 命令中新增immediate，語法如下：

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

這個命令不會影響效能，但它會立即返回到當前的session，處理kill操作，而不是等待其他的資訊完成。  如果session 一直處於killed 狀態，那麼可以考慮在作業系統級別kill掉相關的程式。不過在操作之前，要先確認session 是否在執行rollback 操作。 可以使用如下SQL 來確認。

SET LINESIZE 200

COLUMN username FORMAT A15

SELECT s.username,

      s.sid,

      s.serial#,

      t.used_ublk,

      t.used_urec,

      rs.segment_name,

      r.rssize,

      r.status

FROM v$transaction t,

      v$session s,

      v$rollstat r,

      dba_rollback_segs rs

WHERE s.saddr = t.ses_addr

AND   t.xidusn = r.usn

AND   rs.segment_id = t.xidusn

ORDER BY t.used_ublk DESC;

如果有我們的session，那麼就要等rollback 先完成，然後才能在作業系統級別kill session。

kill session 的時候僅僅是將會話殺掉。在有些時候，由於較大的事務或需要運行較長的SQL語句將導致需要kill的session並不能立即殺掉。對於這種情況將收到 "marked for kill"提示(如下)，一旦會話當前事務或操作完成，該會話被立即殺掉。

    alter system kill session '4730,39171'

    *

    ERROR at line 1:

    ORA-00031: session marked for kill

  在下面的操作中將殺掉會話146，144

    sys@AUSTIN> alter system kill session '146,23';

    System altered.

    sys@AUSTIN> alter system kill session '144,42';

    System altered.

    sys@AUSTIN> select inst_id,saddr,sid,serial#,paddr,username,status,program from gv$session where username is not null;

       INST_ID SADDR           SID    SERIAL# PADDR    USERNAME   STATUS   PROGRAM

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

             1 4C70BF04        144         42 4C6545A0 SCOTT      KILLED   sqlplus@oracle10g (TNS V1-V3)

             1 4C70E6B4        146         23 4C6545A0 TEST       KILLED   sqlplus@oracle10g (TNS V1-V3)

             1 4C71FC84        160         17 4C624174 SYS        ACTIVE   sqlplus@oracle10g (TNS V1-V3)

SQL> select sid,serial#,server,status from v$session where sid=22;

       SID    SERIAL# SERVER    STATUS

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

        22          7 PSEUDO    KILLED              

注意：在查詢中可以看到被殺掉的會話的PADDR地址發生了變化，參照查詢結果中的紅色字型。如果多個session被kill 掉，則多個session的PADDR被改為相同的進程地址，被殺掉的會話的server列變為PSEUDO。

1.3.4.1  oracle killed會話不釋放的問題

一般情況下，在殺一個會話的時候，直接執行alter system kill session ‘sid,serial#’;

Administrator's Guide說，當session是active的時候，alter system kill session 只是將session的狀態標識為killed，server變為pseudo狀態，並不會釋放session持有的資源，所以我們在執行完alter system kill session 後，看會話還是一直存在。

這種情況下可以使用 immediate選項，強制立即Kill會話，如下：

SQL> alter system kill session '3964,51752' immediate;

  SQL Language Reference（）

裡對Immediate的解釋是：IMMEDIATE Specify IMMEDIATE to instruct

Database to roll back ongoing transactions, release all session locks, recover the entire session state,and return control to you immediately.

1.3.4.2  killed狀態的會話如何找到spid列

一般情況下我們查詢會話的後臺程式是透過如下的SQL，即透過v$session的paddr列關聯v$process的addr列，但是killed狀態的v$session的paddr列都變成了一樣的，所以已經沒有辦法透過如下的SQL去查詢了。

SELECT b.SID,

       b.SERIAL# ,

       c.SPID,

       b.status

FROM   v$session b ,

       v$process c

WHERE  b.PADDR=c.ADDR

AND   b.sid=???;

如果會話已經在v$sesion裡是killed狀態，那麼我們透過上面的SQL已經查不出spid,可以用下面的SQL查出SPID：

-----方法1

select spid, program from v$process

    where program!= 'PSEUDO'

    and addr not in (select paddr from v$session)

    and addr not in (select paddr from v$bgprocess)

    and addr not in (select paddr from v$shared_server);

select INST_ID, spid, program,'kill -9 '|| spid  kill9

  from gv$process a

where program != 'PSEUDO'

   and (INST_ID, addr) not in (select INST_ID, paddr from gv$session)

   and (INST_ID, addr) not in (select INST_ID, paddr from gv$bgprocess)

   and (INST_ID, addr) not in (select INST_ID, paddr from gv$shared_server)

   and a.PNAME is null;

-----方法2 我自己常用的方法

set line 9999

col sessionid format a20

col sessionid_killed format a20

col kill_session format a60

SELECT a.INST_ID,

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

       (select spid

          from gv$process b

         where b.INST_ID = a.INST_ID

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

        ) sessionid,

       a.PADDR,

       a.STATUS,

       a.PROGRAM,

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

  FROM gv$session a

WHERE a.USERNAME = 'SYS'

   and a.STATUS = 'KILLED';

As a result of the bug, 2 additional columns have been added to V$SESSION from 11g on:

V$SESSION

CREATOR_ADDR - state object address of creating process

CREATOR_SERIAL# - serial number of creating process

CREATOR_ADDR is the column that can be joined with the ADDR column in V$PROCESS to uniquely identify the killed process corresponding to the former session.

Following the previous example, this would identify the killed session

-----方法3

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

       (select spid

          from gv$process b

         where b.INST_ID = a.INST_ID

           and A.pid = b.pid) sessionid,

       'alter system kill session ''' || sid || ',' || serial# || ''';' kill_session

  FROM gV$DETACHED_SESSION a;

-----方法4

SELECT INST_ID, spid, program, 'kill -9 ' || spid kill9

  FROM gv$process a

WHERE (a.INST_ID, a.addr) in (select INST_ID, p.addr

                                 from gv$process p

                                where pid <> 1

                               minus

                               select INST_ID, s.paddr

                                 from gv$session s)

    and a.PNAME is null;

-----方法4

SELECT s.SID, s.username,s.status,

x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,

decode(bitand(x.ksuprflg,2),0,null,1)

FROM x$ksupr x,v$session s

WHERE s.paddr(+)=x.addr

and bitand(ksspaflg,1)!=0 

; 

1.3.4.3  為何killed狀態的程式一直在v$session中能查詢到？

[ID 100859.1]這篇文章中提到pmon如何清理killed的會話：

PMON will not delete the session object itself until the client connected to

that session notices that it has been killed.  Therefore, the sequence of

events is:

1) alter system kill session is issued - the STATUS of the session object in

   V$SESSION becomes KILLED, its server becomes PSEUDO.

2) PMON cleans up the *resources* allocated to the session

   (i.e., rolls back its transaction, releases its locks, etc).

3) the entry in V$SESSION remains there until the client of that session (the

   client is the process associated with the OSUSER,MACHINE,PROCESS columns in

   the V$SESSION view) tries to do another request.

4) the client attempts another SQL statement and gets back ORA-28.

5) PMON can now remove the entry from V$SESSION.

   This behavior is necessary because the client still has pointers to the

   session object even though the session has been killed.  Therefore, the

   object cannot be deleted until the client is no longer pointing at it.

alter system kill session後，會話狀態變為killed，pmon回收資源後，會話的資訊仍然保留在v$session中，直到客戶端再次請求，資料庫返回ORA-28錯誤："your session has been killed"。

此時pmon才從v$session中移除這些會話的資訊。而上述中，客戶端機器重啟，無法再向資料庫傳送請求，因此從v$session中一直可以查到，只有透過作業系統強制殺掉程式，才能觸發pmon從v$session中清除。

然後Oracle就等待PMON去清除這些Session.所以通常等待一個被標記為Killed的Session退出需要花費很長的時間.如果此時被Kill的process，重新嘗試執行任務，那麼馬上會收到程式中斷的提示，process退出，此時Oracle會立即啟動PMON來清除該session.這被作為一次異常中斷處理.

1.3.4.4  為何kill session後，paddr變成同一個值了？

[ID 387077.1]這篇文章中提到：

Bug 5453737 WHEN A SESSION IS KILLED, PADDR CHANGES IN V$SESSION BUT ADDR NOT IN V$PROCESS closed as not a bug with the following explanation:

When a session is killed, the session state object(and all the child state objects under the session state object) move out from under the original parent process state object, and are placed under the pseudo process state object (which is expected, given the parent/child process mechanism on Unix). PMON will clean up all the state objects found under the pseudo process state object. That explains why PADDR changes in V$SESSION when a session is killed. New PADDR you are seeing in v$SESSION is the address of the pseudo process state object. This shows up in system state under PSEUDO PROCESS for group DEFAULT: V$PROCESS still maintains the record of the original parent process. This is expected.

使用以下sql查詢killed狀態的spid：

select spid, program from v$process

    where program!= 'PSEUDO'

    and addr not in (select paddr from v$session)

    and addr not in (select paddr from v$bgprocess)

    and addr not in (select paddr from v$shared_server);

透過底層表x$ksupr關聯v$session也可以，11.1.0.6版本及更高在v$session增加了2個欄位CREATOR_ADDR和CREATOR_SERIAL#用於標記這種情況。然後就可以透過作業系統命令kill -9來殺掉這些程式了。

1.3.4.5  ALTER SYSTEM KILL SESSION 許可權不足

需要賦許可權，dba角色不用，grant alter system to lhr;

1.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.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.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.1  MOS上的一些資料

於是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

---比較有用的

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  實驗環境介紹

二.2  實驗內容

二.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）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2121019/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● QQ群：230161599     微信群：私聊 ● 聯絡我請加QQ好友(646634621)，註明新增緣由 ● 於 2016-06-15 10:00~ 2016-06-26 19:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................... 拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。