Oracle停止job的正确方式

Oracle停止job的正确方式

1、改job状态broken由N为Y
2、删除job

-- Oracle停止job的正确方式

-- 先修改broken状态为Y，再杀会话。

-- 1、改job状态broken由N为Y
-- 查询job信息
select * from dba_jobs

select * from dba_jobs_running;

-- 修改job状态停止job
begin
  dbms_job.broken(job_id,true,sysdate);
  commit;
end;

-- 批量停止job
DECLARE
  v_job NUMBER;
BEGIN
  FOR v IN (SELECT job 
              FROM user_jobs
             WHERE what LIKE '%sp_for_all_new%') LOOP
    dbms_job.broken(v.job,TRUE,SYSDATE);
    COMMIT;
  END LOOP;
  COMMIT;
END;

-- 批量删除job
DECLARE
  v_job NUMBER;
BEGIN
  FOR v IN (SELECT job 
              FROM user_jobs
             WHERE what LIKE '%sp_for_all_new%') LOOP
    dbms_job.remove(v.job);
    COMMIT;
  END LOOP;
  COMMIT;
END;

-- 2、杀掉被锁住的job会话（kill lock job session）
SELECT DISTINCT ''''||a.SID||','||a.SERIAL#||',@'||a.INST_ID||'''' AS si_id,a.*,b.*
  FROM gv$session a
      ,(SELECT v.sid,v.ID2 JOB,v.INST_ID inst_id
          FROM sys.job$ j
              ,gv$lock v
         WHERE v.type = 'JQ'
           AND j.job(+) = v.ID2) b
      ,gv$instance c
 WHERE a.INST_ID = b.inst_id
   AND a.SID = b.sid
   AND a.INST_ID = c.INST_ID
   AND c.INST_ID = b.inst_id
   AND b.job = 86441021;

-- 查询执行的sql信息
SELECT * FROM v$sqlarea WHERE sql_id = 'xxxxxx';