watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk= watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=       

    What we share with you today is the Oracle in , How to clean up regularly INACTIVE State of the conversation ?


stay Oracle in , How to clean up regularly INACTIVE State of the conversation ?          



In general , A small amount of INACTVIE The session has no effect on the database , however , If a large number of sessions appear in the database due to some reasons such as program design, etc., it will be in the INACTIVE state , That will lead to a lot of system resources being consumed , The number of sessions exceeds that of the system SESSION The maximum of , appear ORA-00018:maximum number of sessions exceeded error . At this point, you need to clean up those who have been in for a long time INACTIVE State of the conversation . Man made periodic inspection 、 It's not realistic to kill this kind of conversation , Clean up regularly those who have been in for a long time INACTIVE Conversation , There are several ways to use :

1. sqlnet.ora Add... To the document sqlnet.expire_time, The unit is minutes .

2.  Set user profile Of IDLE_TIME Parameters , Need to set up resource_limit by true, And then set IDLE_TIME Parameters , In minutes :

alter system set resource_limit=true;

alter profile default limit idle_time 10;

Method 2 Needs and methods 1 Use a combination of .

3.  direct KILL fall INACTIVE Conversation .V$SESSION In the view LAST_CALL_ET Field represents the time from the end of the user's last statement to sysdate Time for , The unit is in seconds . Every time the user executes a new statement , The field is reset to 0, Start counting again . You can use this field to get the idle time after the last operation of the database . This method is recommended to release INACTIVE State of the conversation . The specific code is as follows :

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:   kill 10 An hour ago conversation , The alarm log will record the killed session information

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


    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        => ' Delete -- Every time 60 Check every minute ');

END;

/