SQL code
--作用:建立JOB定期清理KILLED的SESSION
--注意:此脚本必须以SYS用户授予当前用户select on v_$session权限
--创建JOB
DECLARE
n_job NUMBER;
BEGIN
dbms_job.submit(job => n_job,
what => 'BEGIN
FOR rc IN (SELECT sid,serial# FROM v$session WHERE status = ''KILLED'') LOOP
EXECUTE IMMEDIATE ''ALTER SYSTEM KILL SESSION '''':sid,:serial#'''' IMMEDIATE'' USING rc.sid,rc.serial#;
END LOOP;
END;',
next_date =>SYSDATE, --现在开始
INTERVAL => 'SYSDATE+10/(24*3600)'); --每10秒执行一次
COMMIT;
END;
/
--移除JOB
BEGIN
DBMS_JOB.REMOVE(65);
COMMIT;
END;
/
--确认是否正常运行
SELECT last_sec,failures FROM user_jobs WHERE job = 65;
--作用:建立JOB定期清理KILLED的SESSION
--注意:此脚本必须以SYS用户授予当前用户select on v_$session权限
--创建JOB
DECLARE
n_job NUMBER;
BEGIN
dbms_job.submit(job => n_job,
what => 'BEGIN
FOR rc IN (SELECT sid,serial# FROM v$session WHERE status = ''KILLED'') LOOP
EXECUTE IMMEDIATE ''ALTER SYSTEM KILL SESSION '''':sid,:serial#'''' IMMEDIATE'' USING rc.sid,rc.serial#;
END LOOP;
END;',
next_date =>SYSDATE, --现在开始
INTERVAL => 'SYSDATE+10/(24*3600)'); --每10秒执行一次
COMMIT;
END;
/
--移除JOB
BEGIN
DBMS_JOB.REMOVE(65);
COMMIT;
END;
/
--确认是否正常运行
SELECT last_sec,failures FROM user_jobs WHERE job = 65;