JOB定时kill长时间无活动session

1、创建过程

CREATE OR REPLACE PROCEDURE P_MONITOR(AN_MINUTES NUMBER DEFAULT 60) IS
 --DECLARE
  CURSOR C_USERS IS SELECT USERNAME,STATUS, MACHINE,
         'ALTER SYSTEM KILL SESSION '||''''||SID||','||SERIAL# ||'''' OPERATES
         FROM V$SESSION S WHERE TYPE = 'USER' AND STATUS = 'INACTIVE' AND
        LAST_CALL_ET > 60 * 60 * 1 AND MACHINE NOT LIKE '%ZS-ASRSERVER1%'
         AND MACHINE NOT LIKE '%ZSKJ-2%' AND MACHINE NOT LIKE '%WAIWANGCHAXUN%'
         AND MACHINE NOT LIKE '%ZONGSHEN%' ORDER BY LAST_CALL_ET DESC;
  T_USERS C_USERS%ROWTYPE;
  V_STR VARCHAR2(400);
  BEGIN
   OPEN C_USERS;
     LOOP
       FETCH C_USERS INTO T_USERS;
       EXIT WHEN C_USERS%NOTFOUND;
       V_STR := T_USERS.OPERATES;
       EXECUTE IMMEDIATE V_STR;
     END LOOP;
   CLOSE C_USERS;
 END P_MONITOR;
/

2、创建JOB来执行过程

--以sys用户登陆sqlplus或plsql的command窗口中执行下列命令
execute dbms_job.run(:job)//开始
execute dbms_job.broken(:job,true,next_date)//停止一个job,next_date（某一时刻停止,不要此参数立刻停）。
execute dbms_job.broken(:job,false,next_date)//停止后重新启动job需要先把broken false掉
execute dbms_job.remove(:job)//删除
以上命令执行后都需commit

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/755829/viewspace-563055/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/755829/viewspace-563055/