alter system set resource_limit=true;

alter profile default limit idle_time 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個小時之前的會話 ,告警日誌中會記錄被殺掉的會話資訊

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

 

    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;

/