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'; |
|手机版|小黑屋|梦想之都-俊月星空 ( 粤ICP备18056059号 )
GMT+8, 2024-9-19 15:00 , Processed in 0.025253 second(s), 17 queries .
Powered by Discuz! X3.5
© 2001-2024 Discuz! Team.