oracle job详解

/***JOB***/

--23 JOB命令提交定时调度

（1.V_JOB数字类型可以空，2.提交作业的存储过程注意分号';'，3.作业执行时间，4.下一次作业调度时间-该参数可不写-即只填三个参数）

DECLARE
V_JOB NUMBER; --即使执行确定的JOB_ID也没用，会被替换成系统生成的JOB_ID
BEGIN
      DBMS_JOB.SUBMIT(V_JOB,
                      'P_PROCEDURE;',
                      SYSDATE,
                      'sysdate+10/(24*60)');--设置定时调度，下一次执行时间10分钟后继续调用本存储过程
      COMMIT;
END;

注意事项：

ORACLE JOB(当次执行时间比现在时间要小，定义的JOB会立即启动执行)

（例如当前时间是2019/10/24 18：55，trunc(sysdate)+7/24=2019/10/24 07：00）

begin
  dbms_job.change(1,'P_PROCEDURE;',trunc(sysdate)+7/24,'trunc(sysdate)+1+7/24');
  COMMIT;
END;

--24 删除JOB

先获取JOB号码

SELECT JOB INTO V_JOB FROM ALL_JOBS;
然后执行

SQL>execute dbms_job.remove(V_JOB);
SQL>commit;

--4 查找作业SQL（24）

SELECT W.JOB,W.BROKEN,W.WHAT,W.* FROM USER_JOBS W
 WHERE W.WHAT LIKE 'P_PROCEDURE%'
 ORDER BY W.JOB ASC;

--5 命令窗口下执行（24）

SQL>EXECUTE DBMS_JOB.REMOVE(W.JOB);
SQL>COMMIT;

--6 停止JOB

-- 查找JOB_ID(JOB列)
select * from dba_jobs;
SQL>exec dbms_job.broken(21,true);
SQL>commit;

--7 启动JOB(注意：有个大坑！！！如果直接将BROKEN置为N,则会立即启动JOB,必须要小心谨慎地使用！！！)

-- 查找JOB_ID(JOB列)
select * from dba_jobs;
SQL>exec dbms_job.broken(21,false);
SQL>commit;

--8 通过JOB里面调度的存储，正在执行的SQL模糊查询进程号ID

   SELECT S.STATUS, S.USERNAME,
                 S.SID,
                 S.SERIAL#,
                 S.INST_ID,
                 S.EVENT,
                 S.WAIT_CLASS,
                 S.TERMINAL,
                 S.PROGRAM,
                 S.SQL_EXEC_START,
                 S.LOGON_TIME,
                 S.ACTION,
                 SQ.SQL_TEXT
            FROM GV$SESSION S, GV$SQLAREA SQ
           WHERE S.STATUS = 'ACTIVE'
             --AND S.USERNAME IS NOT NULL
             AND S.SQL_ID = SQ.SQL_ID
             --AND S.ACTION IS NULL
            -- And S.evEnt='cursor: mutex X'
             --AND SQ.ACTION = 'Primary Session'
             --AND SQ.SQL_TEXT LIKE '%commit%'
             --AND S.ACTION = 'P_PROCEDURE';
             AND lower(SQ.SQL_TEXT) LIKE '%P_PROCEDURE%';

--9 停止正在运行JOB的步骤

1.先查找正在运行JOB的JOB_ID

select * from dba_job;

2.将正在运行的JOB删除或者BROKEN置为Y（第2步骤没有执行，直接执行第3步骤会发生JOB被重新调度）（如果BROKEN置为Y,下次重新执行为N时,JOB会立即执行，这一步需谨慎！！！）

--删除job
begin
dbms_job.remove(v_job_id);
commit;
end;
或者
--将job置broken = Y
begin
dbms_job.broken(v_job_id,true);
commit;
end;

3.查找运行JOB的进程号ID，可以查看会话窗口查询activity_session观察是哪一个进程，进一步执行会话删除

--41.停止正在运行JOB的操作步骤（先获取是什么JOB在跑）（完整版）

--1 先获取是什么JOB在跑,查找到需要的JOB

select R.JOB, J.WHAT from DBA_JOBS_RUNNING R, DBA_JOBS J
 where R.JOB = J.JOB;

--2 将该JOB删除或者BROKEN置为Y

BEGIN
DBMS_JOB.REMOVE(R.JOB);
COMMIT;
END;

-- 或

BEGIN
DBMS_JOB.BROKEN(R.JOB, TRUE);
COMMIT;
END;

--3 获取删除该进程脚本，然后执行（执行用户需要ALTER SYSTEM权限）

select 'alter system kill session '''||S.SID||','||S.SERIAL#||''''||';' KILL掉正在运行的JOB脚本 
  from v$session s, DBA_JOBS_RUNNING R, DBA_JOBS J 
 where S.SID = R.SID
   and R.JOB = J.JOB
   and J.WHAT = 'TEST';

--4 如果进程状态一直为KILLED，则需要到数据库服务器将该进程删除(--到OS层面删除进程 $orakill LODSD SPID(查询出来的OS进程号))

--查询已经kill掉的会话

select * from v$session a where a.status = 'KILLED';

--查询是否存储已经kill掉的会话存在进程在跑

select b.SPID from v$session a, v$process b where a.PADDR = b.ADDR and a.status = 'KILLED';

--到OS层面删除进程

$orakill LODSD SPID(查询出来的OS进程号)

--42.查看BROKEN = 'N'的JOB数量，包含定时调度、非定时调度_正在运行

select count(1) 总量, 
       count(case when j.BROKEN = 'N' then 1 end) N,
       count(case when j.BROKEN = 'N' and j.INTERVAL != 'null' then 1 end) 定时调度,
       count(case when j.BROKEN = 'N' and j.INTERVAL  = 'null' then 1 end) 非定时调度_正在运行
  from dba_jobs j;