清除会话 Oracle,在Oracle中，如何定时清理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;

/