Oracle创建存储过程及定时任务周期性kill掉inactive状态的session

学而不思则罔，思而不学则殆

已于 2023-08-11 19:13:20 修改

阅读量175

点赞数

文章标签： oracle 数据库

于 2023-08-11 18:47:08 首次发布

本文链接：https://blog.csdn.net/mm15922520865/article/details/132238335

版权

1、以管理员身份登录DB

sqlplus / as sysdba

2、创建存储过程–排除rman备份的session

CREATE OR REPLACE PROCEDURE DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS
    JOB_NO       NUMBER;
    NUM_OF_KILLS NUMBER := 0;
BEGIN
    FOR REC IN (SELECT SID, SERIAL#, INST_ID, MODULE, STATUS
                                FROM GV$SESSION S
                             WHERE S.USERNAME IS NOT NULL
                                 AND S.LAST_CALL_ET >= 30 * 60
                                 AND S.STATUS = 'INACTIVE'
                                 AND (S.MODULE NOT LIKE 'rman@%'  or S.MODULE  is null)
                             ORDER BY INST_ID DESC) LOOP
        EXECUTE IMMEDIATE 'alter system disconnect session ''' || REC.SID || ', ' ||
                                            REC.SERIAL# || '''immediate';
        DBMS_OUTPUT.PUT_LINE('. killed locally ' || JOB_NO);
        NUM_OF_KILLS := NUM_OF_KILLS + 1;
    END LOOP;
END DB_KILL_IDLE_CLIENTS;
/

3、创建定时任务

declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  /*自动生成JOB_ID*/  
        WHAT => 'DB_KILL_IDLE_CLIENTS;',  /*需要执行的存储过程名称(大写)或SQL语句*/  
        NEXT_DATE => sysdate+10/(24*60),  /*初次执行时间-下一个10分钟*/  
        INTERVAL => 'trunc(sysdate,''mi'')+10/(24*60)' /*每隔10分钟执行一次*/
      );  
  commit;
end;
/