【DB笔试面试702】在Oracle中，如何定时清理INACTIVE状态的会话？

小麦苗DBA宝典

发布于 2019-12-17 11:35:33

8440

发布于 2019-12-17 11:35:33

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

♣

题目部分

在Oracle中，如何定时清理INACTIVE状态的会话？

♣

答案部分

一般情况下，少量的INACTVIE会话对数据库并没有什么影响，但是，如果由于程序设计等某些原因导致数据库出现大量的会话长时间处于INACTIVE状态，那么将会导致大量的系统资源被消耗，造成会话数超过系统SESSION的最大值，出现ORA-00018:maximum number of sessions exceeded错误。此时就需要清理那些长时间处于INACTIVE状态的会话。人为定期检查、杀掉这类会话肯定不太现实，要定期清理那些长时间处于INACTIVE的会话，可以使用如下几种办法：

1. sqlnet.ora文件里加上sqlnet.expire_time，单位为分钟数。

2. 设置用户profile的IDLE_TIME参数，需要设置resource_limit为true，然后再设置IDLE_TIME参数，单位为分钟：

alter system set resource_limit=true;
alter profile default limit idle_time 10;

方法2需要和方法1结合使用。

3. 直接KILL掉INACTIVE的会话。V$SESSION视图中的LAST_CALL_ET字段表示用户最后一条语句执行完毕后到sysdate的时间，单位为秒。每次用户执行一个新的语句后，该字段复位为0，重新开始记数。可以通过该字段来获得一个连接用户最后一次操作数据库后的空闲时间。推荐使用这种方法来释放INACTIVE状态的会话。具体代码如下所示：

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# || ',@' || a.INST_ID || ''' IMMEDIATE' kill_session
                    FROM   gv$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;
/

& 说明：

有关如何定时清理INACTIVE状态的会话的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2154547/