kill session 是DBA經常碰到的事情之一。如果kill 掉了不該kill 的session，則具有破壞性，因此儘可能的避免這樣的錯誤發生。同時也應當注意，
如果kill 的session屬於oracle 後臺程式，則容易導致資料庫例項當機。
通常情況下，並不需要從作業系統級別殺掉Oracle會話程式，但並非總是如此，下面的描述中給出了在Oracle級別殺掉會話以及作業系統級別殺掉程式。

一、獲得需要kill session的資訊(使用V$SESSION 和 GV$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 146 23 27573 TEST sqlplus@oracle10g (TNS V1-V3) 4C621950 INACTIVE
1 160 17 27610 SYS sqlplus@oracle10g (TNS V1-V3) 4C624174 ACTIVE
1 144 42 27641 SCOTT sqlplus@oracle10g (TNS V1-V3) 4C624730 INACTIVE

二、使用ALTER SYSTEM KILL SESSION 命令實現
語法：
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
SQL Language Reference
裡對Immediate的解釋是：IMMEDIATE Specify IMMEDIATE to instruct Oracle
Database to roll back ongoing transactions, release all session locks, recover the entire session state,
and return control to you immediately.
文章最後有oracle官方文件給出的殺程式文件詳細解釋

對於RAC環境下的kill session ,需要搞清楚需要kill 的session 位於哪個節點，可以查詢GV$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)

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

透過下面的語句來找回被kill 掉的ADDR先前的地址
SELECT 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;

USERNAME STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS D
---------- -------- -------- ---------- ---------- ------------ ---------- -- -
ACTIVE 4C623BB8 99 4 27468 275 EV 1
ACTIVE 4C623040 9 24 27444 0 1
ACTIVE 4C622A84 101 4 27480 274 EV 1
ACTIVE 4C6224C8 1 48 27450 0 1
ACTIVE 4C621F0C 1 48 27450 0 1
ACTIVE 4C6235FC 2 4 27468 0 1
SYS ACTIVE 4C624174 2 15 27442 0
ACTIVE 4C62081C 1 48 27440 0 1
ACTIVE 4C621394 1 48 27440 0 1
ACTIVE 4C620DD8 11 24 27476 0 1
ACTIVE 4C61F6E8 15 4 27610 0 1
ACTIVE 4C620260 222 24 27450 0 1
ACTIVE 4C61FCA4 7 25 27573 0 1
ACTIVE 4C61F12C 6 25 27573 0 1
ACTIVE 4C61EB70 4 24 27458 0 1
ACTIVE 4C61E5B4 1 48 27440 0 1
ACTIVE 4C61DFF8 2 24 27444 0 1
4C624730 0 0 0
4C621950 0 0 0
4C61DA3C 0 0 0

或者根據下面的語句來獲得發生變化的addr
sys@AUSTIN> select p.addr from v$process p where pid <> 1
2 minus
3 select s.paddr from v$session s;

ADDR
--------
4C621950
4C624730

三、在作業系統級別殺掉會話
尋找會話對應的作業系統的程式ID
sys@AUSTIN> select SPID from v$process where ADDR in ('4C621950','4C624730') ;

SPID
----------
27573
27641

使用kill 命令來殺掉作業系統級別程式ID
kill session -9 27573

kill session -9 27641

四、獲得當前會話的SID
SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
627

五、多個會話需要kill 的處理辦法
1.根據給定的SID(使用者名稱)查詢需要殺掉會話的資訊，包括位於哪一個例項
set linesize 160
col program format a35
col username format a18
select inst_id,saddr,sid,serial#,paddr,username,status,program from gv$session
where sid in ('2731','2734','2720','2678','2685')
and username='CTICUST'
order by inst_id;

INST_ID SADDR SID SERIAL# PADDR USERNAME STATUS PROGRAM
---------- ---------------- ---------- ---------- ---------------- ------------------ -------- ---------------------------
1 00000003DAF8F870 2678 8265 00000003DBC6CA08 MSS4USR INACTIVE JDBC Thin Client
1 00000003DAF98E48 2685 83 00000003DBC08510 MSS4USR ACTIVE JDBC Thin Client
1 00000003DAFC7B80 2720 5 00000003DBBEDA20 MSS4USR INACTIVE JDBC Thin Client
1 00000003DAFD66F8 2731 3 00000003DBBE9AE0 SYS ACTIVE racgimon@svdg0028(TNS V1-V3)
1 00000003DAFDA730 2734 15 00000003DBBEC268 MSS4USR INACTIVE JDBC Thin Client
2 00000003DAFD66F8 2731 1 00000003DBBE92F8 ACTIVE oracle@svdg0029 (ARC0)

上面的查詢中有一個SID為2731的位於節點2上。
也可以透過下面的方式來獲得RAC的節點資訊，便於確定需要kill 的session究竟位於哪一個節點。

set linesize 160
col HOST_NAME format a25
SQL> select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STATUS from gv$instance order by 1;

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STATUS
--------------- ---------------- ------------------------- ----------------- ------------
1 O02WMT1A svd0051 10.2.0.4.0 OPEN
2 O02WMT1B svd0052 10.2.0.4.0 OPEN
3 O02WMT1C svd0053 10.2.0.4.0 OPEN

2.使用下面查詢來生成kill session 的語句
select 'alter system kill session '''|| sid ||',' ||SERIAL# ||''''||';' from gv$session
where sid in ('2731','2734','2720','2678','2685')
order by inst_id;

獲得下列kill session的語句，根據要求由於此次需要殺掉的session全部位於節點1，因此登入到節點節點1執行下面的語句

alter system kill session '2678,8265';

alter system kill session '2685,83';

alter system kill session '2720,5';

alter system kill session '2731,3';

alter system kill session '2734,15';

alter system kill session '2731,1'; --此條命令不需要執行，該session位於節點2。

ps.

Terminating Sessions

Sometimes it is necessary to terminate current user sessions. For example, you might want to perform an administrative operation and need to terminate all non-administrative sessions. This section describes the various aspects of terminating sessions, and contains the following topics:

When a session is terminated, any active transactions of the session are rolled back, and resources held by the session (such as locks and memory areas) are immediately released and available to other sessions.

You terminate a current session using the SQL statement ALTER SYSTEM KILL SESSION. The following statement terminates the session whose system identifier is 7 and serial number is 15:

ALTER SYSTEM KILL SESSION '7,15';

Identifying Which Session to Terminate

To identify which session to terminate, specify the session index number and serial number. To identify the system identifier (SID) and serial number of a session, query the V$SESSION dynamic performance view. For example, the following query identifies all sessions for the user jward:

SELECT SID, SERIAL#, STATUS
  FROM V$SESSION
  WHERE USERNAME = 'JWARD';

SID    SERIAL#    STATUS
-----  ---------  --------
    7         15  ACTIVE 
   12         63  INACTIVE

A session is ACTIVE when it is making a SQL call to Oracle Database. A session is INACTIVE if it is not making a SQL call to the database.

Terminating an Active Session

If a user session is processing a transaction (ACTIVE status) when you terminate the session, the transaction is rolled back and the user immediately receives the following message:

ORA-00028: your session has been killed

If, after receiving the ORA-00028 message, a user submits additional statements before reconnecting to the database, Oracle Database returns the following message:

ORA-01012: not logged on

An active session cannot be interrupted when it is performing network I/O or rolling back a transaction. Such a session cannot be terminated until the operation completes. In this case, the session holds all resources until it is terminated. Additionally, the session that issues the ALTER SYSTEM statement to terminate a session waits up to 60 seconds for the session to be terminated. If the operation that cannot be interrupted continues past one minute, the issuer of the ALTER SYSTEM statement receives a message indicating that the session has been marked to be terminated. A session marked to be terminated is indicated in V$SESSION with a status of KILLED and a server that is something other than PSEUDO.

Terminating an Inactive Session

If the session is not making a SQL call to Oracle Database (is INACTIVE) when it is terminated, the ORA-00028 message is not returned immediately. The message is not returned until the user subsequently attempts to use the terminated session.

When an inactive session has been terminated, the STATUS of the session in the V$SESSION view is KILLED. The row for the terminated session is removed from V$SESSION after the user attempts to use the session again and receives the ORA-00028 message.

In the following example, an inactive session is terminated. First, V$SESSION is queried to identify the SID and SERIAL# of the session, and then the session is terminated.

SELECT SID,SERIAL#,STATUS,SERVER
   FROM V$SESSION
   WHERE USERNAME = 'JWARD';

SID    SERIAL#   STATUS     SERVER
-----  --------  ---------  ---------
    7        15  INACTIVE   DEDICATED
   12        63  INACTIVE   DEDICATED
2 rows selected.

ALTER SYSTEM KILL SESSION '7,15';
Statement processed.

SELECT SID, SERIAL#, STATUS, SERVER
   FROM V$SESSION
   WHERE USERNAME = 'JWARD';

SID    SERIAL#   STATUS     SERVER
-----  --------  ---------  ---------
    7        15  KILLED     PSEUDO
   12        63  INACTIVE   DEDICATED
2 rows selected.

轉載：http://blog.csdn.net/leshami/article/details/6439019

Oracle徹底殺掉程式

Terminating Sessions

Identifying Which Session to Terminate

Terminating an Active Session

Terminating an Inactive Session

相關文章