0、前言

  数据库会话分为Active（SQL语句执行中） — Inactive（没有SQL语句执行） — Spined（超过数据库断开时间限制）。

  我们这里要注意的是，不管会话处于何种状态，他都会占据数据库连接的会话数，而Spined状态不管是否断开，他都验证了一个事情，就是这条会话它是长时间未活跃的会话，开发那边的没有设置自动断开或者设置没有成功的源头。

1、环境准备

1.1测试数据库信息

暂不披露

1.2整改数据库登陆显示信息

[oracle@:/home/oracle]$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _date>"

登陆验证是否修改成功

[oracle@:/home/oracle]$ sqlplus / as sysdba

SYS@orcl 2024-05-27 14:19:33>

2、IDLE_TIME参数设置

2.1知识点

  数据库会话的状态变化是 Active（SQL执行时）—— Inactive（未执行SQL语句） —— SNIPED（没有执行任何SQL语句且过了数据库设置的空闲时间）—— Disconnect session（因为数据库的Expired Time参数断开会话或者应用程序设置的时间断开会话）。

  从这里看到，数据库断开会话可以是主动的——开发设置断开，也可以是被动的——数据库设置断开。

2.2查看空闲时间的资源配置信息

select * from dba_profiles where resource_name ='IDLE_TIME';

   	  PROFILE	      RESOURCE_NAME		RESOURCE_TYPE		LIMIT
1	  DEFAULT		IDLE_TIME		KERNEL			UNLIMITED
2	MONITORING_PROFILE	IDLE_TIME		KERNEL			DEFAULT

2.3查看用户的默认组

select username,account_status,profile,initial_rsrc_consumer_group from dba_users where username ='TEXT';

   	USERNAME   ACCOUNT_STATUS	PROFILE	  INITIAL_RSRC_CONSUMER_GROUP
1	  TEXT	        OPEN            DEFAULT      DEFAULT_CONSUMER_GROUP

2.4修改限制空闲时间

alter profile default limit idle_time 1;

  如果是在生产上设置空闲时间，需要和应用上的连接池和保留时间沟通，连接池的断开时间应该小于数据库的空闲时间，以免当应用那边还想执行SQL语句的时候，会话却被数据库断开了。

2.5限制生效

alter system set resource_limit=true scope=both sid='*';

2.6查看限制资源状态是否生效

select name,value from gv$parameter where name='resource_limit';

状态是True就是资源限制生效了。

2.7验证是否到时断开

[oracle@:/home/oracle]$ sqlplus text/text

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 27 11:21:01 2024

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again


SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 11401
Session ID: 1894 Serial number: 10753

  到这个步骤为止，可以说IDLE_TIME的设置的确是成功了，因为只要会话状态变成了SNIPED，就说明设置的没问题。

  可是话说回来，他还是不断开，就代表还在占据会话数，他都是长时间不活跃状态了，肯定不会再去执行SQL语句断开，我证明了会话长时间没有执行语句，但是却没办法主动断开这个会话。

3、EXPIRED_TIME参数设置

3.1设置sqlnet.ora的参数

vim /u01/app/oracle/product/11.2.0/db/network/admin/sqlnet.ora 

SQLNET.EXPIRE_TIME = 10

设置参数，安全起见我把两个节点都设置了这个参数。

这个单位是分钟，而且设置完毕的第一个时间段，是不计算在内的。

3.2重启监听

lsnrclt reload

然后再连接的会话才会断开。

这个时而成功，时而不成功，也不知道咋回事。哪里做的不对？

3.3查询会话的Sql语句

SELECT distinct status,
                round(last_call_et/60,2),
                state,
                event,
                blocking_session BLS,
                ' alter system kill session ''' || b.sid || ',' || b.serial# || ',@' || b.inst_id ||''' immediate;',
                c.cpu_time,
                c.elapsed_TIME,
                c.SQL_TEXT,
                c.SQL_ID,
                b.username,
                b.sid,
                b.serial#,
                b.inst_id,
                logon_time,
                osuser OUSER,
                machine,
                LAST_ACTIVE_TIME
  FROM gv$session b, gv$sqlarea c
WHERE b.SQL_ID = c.SQL_ID
   and b.inst_id = c.inst_id 
   and status='SNIPED'
--and last_call_et > 3600
--and EVENT = 'enq: TX - row lock contention'
--and upper(sql_text )like '%SELECT%'
--and  sid = '2545'
-- and username = '用户'
order by round(last_call_et/60,2) desc, c.SQL_TEXT, b.logon_time;

这个可以查询会话状态时sniped的会话，查询起来非常方便，而且也拼凑了查杀语句的会话。

4、处理方法

4.1手动查杀

select 'alter system kill session '''||sid||','||serial#||',@' || inst_id ||''' immediate;' from gv$session where status='SNIPED' ;

直接干掉会话状态时SNIPED的会话。

4.2服务器层面的脚本

#!/bin/sh
tmpfile=/tmp/.kill_sniped
sqlplus system/manager <<EOF
spool $tmpfile
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
and s.status='SNIPED';
spool off
EOF
for x in `cat $tmpfile | grep "^[0123456789]"`
do
    kill -9 $x
done
rm $tmpfile

有点危险，不太敢用。主要是不懂其中的意思。

4.3创建存储过程

CREATE OR REPLACE PROCEDURE "KILL_SNIPED_SESSION"
authid current_user AS
    v_sid number;
    v_serial number;
    v_inst_id number;
    killer varchar2(1000);
    CURSOR cursor_session_info IS
        SELECT sid, serial#,inst_id
        FROM gv$session
        WHERE type != 'BACKGROUND'
            AND status = 'SNIPED'
            AND last_call_et > 3600
            AND username = 'TEXT';
            --AND machine = 'test';
BEGIN
    OPEN cursor_session_info;
    LOOP
        FETCH cursor_session_info INTO v_sid,  v_serial,v_inst_id;
        EXIT WHEN cursor_session_info%notfound;
        killer := 'alter system disconnect session ''' ||  v_sid || ',' || v_serial || ',@' || v_inst_id ||'''  post_transaction immediate';
        EXECUTE IMMEDIATE killer;
    END LOOP;
    dbms_output.PUT_LINE(cursor_session_info % rowcount ||  ' users with idle_time>2700s have been killed!');
    CLOSE cursor_session_info;
END;
/

last_call_et是

如果会话STATUS当前处于活动状态，则该值表示自会话变为活动状态以来经过的时间（以秒为单位）。

如果会话状态当前为非活动状态，则该值表示自会话变为非活动状态起所经过的时间（以秒为单位）。

grant alter system to TEXT;
grant select on gv_$session to TEXT;

所需的权限，第一个权限太大，不建议赋予，第二个是视图，存储过程需要具体的权限，而不是角色。所以建议用system系统用户去设置。

5、总结