合 Oracle之杀会话kill session的相关问题

前言部分

导读和注意事项

① killed状态的会话如何释放(如何找到后台进程)--重点？
② 授予普通用户可以kill自己用户session的权限
③ kill session和disconnect session的区别
④ v$session.CREATOR_ADDR列的使用
⑤ 对inactive会话的处理(1、sqlnet.ora文件中设置expire_time 参数 2、用户profile的idle_time 参数 3、找到很久没有响应的会话然后kill掉)
⑥ v$session.LAST_CALL_ET的使用
⑦ PMON的清理周期隐含参数"_PKT_PMON_INTERVAL"介绍

本文简介

今天同事杀会话的时候采用了alter system kill session ‘xxx,xxx’的方式，结果杀完后，v$session中还可以查到，就求助我，因为我之前杀会话都是带的immediate的，杀完后会立刻释放，v$session中也查询不到，同事现在的情况就只能杀后台进程了，但paddr列关联不到后台进程，查了下MOS还是给出了一些办法，整理了一下，分享给大家。

相关知识点扫盲

得到当前会话的几个SQL

Session 状态说明

Oracle session 有如下几种状态:

ACTIVE - Session currently executing SQL

INACTIVE

KILLED - Session marked to be killed

CACHED - Session temporarily cached for use by Oracle*XA

SNIPED - Session inactive, waiting on the client
有关状态的说明：
（1）active 处于此状态的会话，表示正在执行，处于活动状态。
官方文档说明：
Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.
（2）killed处于此状态的会话，被标注为删除，表示出现了错误，正在回滚。
当然，也是占用系统资源的。还有一点就是，killed的状态一般会持续较长时间，而且用windows下的工具pl/sql developer来kill掉，是不管用的，要用命令：alter system kill session 'sid,serial#' ;
（3）inactive 处于此状态的会话表示不是正在执行的
该状态处于等待操作(即等待需要执行的SQL语句)，通常当DML语句已经完成。 但连接没有释放，这个可能是程序中没有释放，如果是使用中间件来连接的话，也可能是中间件的配置或者是bug 导致。inactive对数据库本身没有什么影响，但是如果程序没有及时commit，那么就会造成占用过多会话。容易是DB 的session 达到极限值。
一般不处理inactive 状态的session， 如果达到了session 的最大值， 就增加processes 和 sessions 参数。 对于Inactive 状态的session，可以设置过期时间：
（1）sqlnet.ora文件中设置expire_time 参数
（2）设置用户profile的idle_time 参数
（3）找到很久没有响应的会话然后kill掉
当设置了resource_limit=true 。通过idle_time限制session idle 时间。session idle超过设置时间，状态为sniped (v$session).,然而OS下的process并不会释放，当session(user process) 再次与server process 通讯，将关闭相应的server process.
sqlnet.expire_time 的原理不一样，Oracle Server 发送包探测dead connection ，如果连接关闭，或者不再用，则关闭相应的server process.
以上两者组合使用，减少server process,防止process超过init$ORACLE_SID极限值。
本文会对这3种方式分别说明的。

清理inactive会话的3种方式

设置sqlnet.expire_time

可以在sqlnet.ora文件里面加上sqlnet.expire_time这个参数来解决，设置一个分钟数，这是ORACLE建议的DCD解决方法。
在 sqlnet.ora文件中设置expire_time 参数
官网有关这个参数的说明：
http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm
Oracle® Database Net Services Reference 11g Release 2 (11.2)E10835-10
SQLNET.EXPIRE_TIME
Purpose
Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.
sqlnet.expire_time 的原理：Oracle Server 发送包探测dead connection ，如果连接关闭，或者不再用，则关闭相应的server process.
Limitations on using this terminated connection detection feature are:
（1）It is not allowed on bequeathed connections.
（2）Though very small, a probe packet generates additional traffic that may downgrade network performance.
（3）Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.
Default ：0
Minimum Value ：0
Recommended Value ：10
Example
SQLNET.EXPIRE_TIME=10

设置用户profile的idle_time 参数

Oracle 用户 profile 属性 http://blog.csdn.net/tianlesoftware/archive/2011/03/10/6238279.aspx 注意，要启用idle_time 要先启用RESOURCE_LIMIT参数。 该参数默认是False。 官网说明如下： RESOURCE_LIMIT	Property	Description
Parameter type	Boolean
Default value	false
Modifiable	ALTER SYSTEM
Range of values	true | false

RESOURCE_LIMIT determines whether resource limits are enforced in database profiles.
Values:
TRUE：Enables the enforcement of resource limits
FALSE：Disables the enforcement of resource limits

找到很久没有响应的会话然后kill掉

关于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个小时之前的会话 ,告警日志中会记录被杀掉的会话信息 ----------------------------------------------------------------------------------- EGIN -- 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';

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

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个小时之前的会话 ,告警日志中会记录被杀掉的会话信息 ----------------------------------------------------------------------------------- EGIN -- 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';

运行日志：

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

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

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.

  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

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

  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 来确认。

如果有我们的session，那么就要等rollback 先完成，然后才能在操作系统级别kill session。
kill session 的时候仅仅是将会话杀掉。在有些时候，由于较大的事务或需要运行较长的SQL语句将导致需要kill的session并不能立即杀掉。对于这种情况将收到 "marked for kill"提示(如下)，一旦会话当前事务或操作完成，该会话被立即杀掉。

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。