1.dba job

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
---- 查看job 201的信息
select * from dba_jobs where job=201;

---- 按最新执行情况排序 
select what,FAILURES,LAST_DATE,NEXT_DATE from dba_jobs order by LAST_DATE desc;

---- 查看job 201执行情况
select what,FAILURES,LAST_DATE,NEXT_DATE from dba_jobs where job=201;

----正在runing的job
 select /*+rule */  sid,job,FAILURES,LAST_DATE,THIS_DATE from dba_jobs_running;
 
----按job内容模糊匹配查询
 select * from dba_jobs dj where dj.WHAT like '%mes_cs_base_product_data%';

---- broken job，停止job

begin
 DBMS_JOB.BROKEN(job#,TRUE); /*停止一个job,里面参数true也可是false，next_date（某一时刻停止）也可是sysdate（立刻停止）。   */
 commit ;---必须提交否则停止无效
  end;

---- 手工执行job

begin
 DBMS_JOB.RUN(job#);/* job的id*/
commit;  
 end;

---- 删除JOb

begin
dbms_job.remove(job#);  /*删除自动执行的job*/
commit;
 end;

---- 批量删除jobs

set line 300
set pages 2000
col schema_user for a20
col DROP_JOBS_SCRIPT for a50
select schema_user,
         'begin' || chr(10) ||'sys.dbms_ijob.remove('||job ||');' || chr(10) ||'commit;'||chr(10)||'end;'   as drop_jobs_script    
from dba_jobs; 

2.scheduler_jobs

----查看job信息
SELECT job_name,program_name,schedule_name,start_date,repeat_interval,
    end_date,state,run_count,NEXT_RUN_DATE
    from dba_scheduler_jobs where job_name='Synchronize_STB';

SELECT OWNER, JOB_NAME, START_DATE, END_DATE, REPEAT_INTERVAL
FROM DBA_SCHEDULER_JOBS
WHERE ENABLED='TRUE'
AND OWNER not in ('SYS','EXFSYS','ORACLE_OCM');

---- 正在运行的job
select * from  DBA_SCHEDULER_RUNNING_JOBS;

----DISABLE schedule job
ENABLED ：指定任务是否启用，默认值为FALSE。FALSE状态表示该任务并不会被执行，除非被用户手动调用，或者用户将该任务的状态修改为TRUE。
EXECUTE DBMS_SCHEDULER.DISABLE(<job_name>);
----启用Jobs
exec dbms_scheduler.enable('job_name');
----禁用Jobs
exec dbms_scheduler.disable('job_name');
----手工执行Jobs
exec dbms_scheduler.run_job('job_name');
----停止Jobs，停止当前正在执行的任务
exec dbms_scheduler.stop_job('job_name');
----删除Jobs
exec dbms_scheduler.drop_job('job_name');

3.停止一个正在运行的job的过程

----1. 查找到正在运行的JOB的spid,sid,serial#:

 select /*+rule */  * from dba_jobs_running;
 select a.spid,b.sid,b.serial# from v$process a ,v$session b where a.addr=b.paddr and b.sid in (select /*+rule */  sid from dba_jobs_running);

----2. Broken你确认的JOB

 ---- 注意使用DBMS_JOB包来标识你的JOB为BROKEN。
     EXEC DBMS_JOB.BROKEN(job#,TRUE);
     commit ;
  ---- 注意：当执行完该命令你选择的这个JOB还是在运行着的。

----3. Kill 对应的Oracle Session

---- 因为BROKEN后该JOB还在运行，如果要求该JOB立刻停止，就需要找到该job的对应SESSION(SID,SERIAL#)，然后执行以下命令：

    ALTER SYSTEM KILL SESSION 'sid,serial#' immediate;

---- 或者直接KILL对应的操作系统的SESSION，如果使用ALTER SYSTEM KILL SESSION执行很长时间，其实可以使用OS的命令来快速KILL掉SESSION.
        For Windows, at the DOS Prompt: orakill sid spid
        For UNIX at the command line> kill –9 spid

----4. 检查你的JOB是否还在运行

select /*+rule */  * from dba_jobs_running;

4. 其他批量操作脚本

--杀掉运行中指定的job
SELECT /*+ rule */
'alter system kill session ''' || s.sid || ',' || s.serial# ||
'''immediate;' kill_script
 FROM v$session s
WHERE s.sid IN (SELECT r.sid
                  FROM dba_jobs_running r
                 WHERE r.job IN (1353,
                                 1345));
                                 


--杀掉job的运行session
BEGIN
 FOR c IN (SELECT 'alter system kill session ''' || s.sid || ',' ||
                  s.serial# || ',@' || s.inst_id || ''' immediate' kill_session_script,
                  s.*
             FROM gv$session s
            WHERE s.status = 'ACTIVE'
              AND s.username NOT IN ('SYS',
                                     'SYSTEM')
              AND s.sid IN (SELECT /*+ rule */ dj.sid
                              FROM dba_jobs_running dj
                             WHERE dj.job in (515945,515948)
                             ))
 LOOP
   BEGIN
     dbms_output.put_line(c.kill_session_script);
     EXECUTE IMMEDIATE c.kill_session_script;
   EXCEPTION
     WHEN OTHERS THEN
       NULL;
   END;
 
 END LOOP;
END;






--杀掉运行时间超过24小时，等待事件为 SQL*Net message from dblink 的job会话
BEGIN
 FOR c IN (SELECT 'alter system kill session ''' || s.sid || ',' ||
                  s.serial# || ',@' || s.inst_id || ''' immediate' kill_session_script,
                  s.*
             FROM gv$session s
            WHERE s.status = 'ACTIVE'
              AND s.username NOT IN ('SYS',
                                     'SYSTEM')
              AND s.event = 'SQL*Net message from dblink'
              AND s.sid IN (SELECT /*+ rule */
                             dj.sid
                              FROM dba_jobs_running dj
                             WHERE dj.last_date <= SYSDATE - 1))
 LOOP
   BEGIN
     dbms_output.put_line(c.kill_session_script);
     EXECUTE IMMEDIATE c.kill_session_script;
   EXCEPTION
     WHEN OTHERS THEN
       NULL;
   END;
 
 END LOOP;
END;







--停止所有非数据库自身job
BEGIN
 FOR c_job IN (SELECT dj.*
                 FROM dba_jobs dj
                WHERE dj.log_user NOT LIKE 'SYS%')
 LOOP
   dbms_ijob.broken(job    => c_job.job,
                   broken => TRUE);
 
   dbms_output.put_line('broken job name ' || c_job.what);
 
 END LOOP;
END;



--导出job
DECLARE
 v_jobtext VARCHAR2(500);
BEGIN
 FOR i IN (SELECT *
             FROM dba_jobs dj where dj.LOG_USER not like 'SYS%')
 
 LOOP
 
   v_jobtext := '';
 
   dbms_job.user_export(i.job,
                        v_jobtext);
 
   dbms_output.put_line(v_jobtext);
 
 END LOOP;
 ROLLBACK;
END;



--以sys删除其他用户的job
BEGIN
 FOR c_job IN (SELECT dj.*
                 FROM dba_jobs dj
                WHERE dj.log_user LIKE '%SRC%')
 LOOP
 
   dbms_ijob.remove(job => c_job.job);
 
   dbms_output.put_line('broken job name ' || c_job.what);
 
 END LOOP;
END;
/

SELECT *  FROM dba_jobs;