9.ORACLE 定期清理SNIPED的过期会话 存储过程

ORACLE 定期清理SNIPED的过期会话 存储过程

#创建存储过程

CREATE OR REPLACE PROCEDURE kill_sniped_session AS
  l_script VARCHAR2(400);
BEGIN
  FOR c IN (SELECT /*+ rule */
             s.sid,
             s.serial#
              FROM v$session s
             WHERE s.status = 'SNIPED'
               AND s.server = 'DEDICATED'
               AND s.username = 'PROD01')
  LOOP
    l_script := 'ALTER SYSTEM disconnect SESSION ''' || c.sid || ',' ||
                c.serial# || ''' immediate';
   -- dbms_output.put_line(l_script);
    EXECUTE IMMEDIATE l_script;
  END LOOP;
END;

#创建JOB每天执行存储过程

DECLARE
  l_job_number NUMBER := 5001;
  
BEGIN
 
  dbms_job.isubmit(job       => l_job_number,
                   what      =>
                   '--定期清理SNIPED的过期会话
				   kill_sniped_session;',
                   next_date => to_date('25-10-2017 17:30:00','dd-mm-yyyy hh24:mi:ss'),
                   INTERVAL  => 'trunc(sysdate+1)+17/24'
                   ); --每天17点执行
 
commit;
END;