1、查询系统JOB

select job,

what,

to_char(next_date, 'yyyy/mm/dd hh24:mi:ss'),

broken,

failures,

schema_user,

log_user,

priv_user

from dba_jobs;

2、查询某个JOB的详细信息(JOB名称如果有前缀用户名的要去掉才能查到)

select *

from user_scheduler_job_log

where job_name = 'FEE_SPLIT_SCHEDULE_JOB'

3、根据上面查到的LOG_ID信息查询JOB日志详细信息

select * from user_scheduler_job_run_details where log_id = 100555;

4、查询JOB失败次数

select job,what,failures,broken from user_jobs

5、查询所有的job：(包括系统和用户的JOB)

select b.name username, d.obj#, a.name job_name, d.failure_count

from sys.obj$ a, sys.user$ b, sys.obj$ c, sys.scheduler$_job d

where d.obj# = a.obj#

AND a.owner# = b.user#

AND d.class_oid = c.obj#(+)

6、查询正在运行的job：

SELECT SID,JOB FROM DBA_JOBS_RUNNING;

7、创建Job

(1)创建存储过程

create  procedure glog_job_pro   as

begin

insert into...

end glog_job_pro;

(2)创建job

declare  glog_job number;

begin

dbms_job.submit(glog_job,'glog_job_pro;',sysdate,'TRUNC(next_day(sysdate,'星期日'))+8/24');

commit;

end;

注意：如果第三个参数设置了sysdate，则默认创建Job之后立刻执行

begin

sys.dbms_scheduler.create_job(job_name            => 'GLOG.FEE_SPLIT_SCHEDULE_JOB',

job_type            => 'STORED_PROCEDURE',

job_action          => 'GLOG.FEE_SPLIT_AUTO_RUNNING',

start_date          => to_date('16-11-2015 05:12:00', 'dd-mm-yyyy hh24:mi:ss'),

repeat_interval     => 'Freq=HOURLY;Interval=8',

end_date            => to_date('16-11-2055 05:12:00', 'dd-mm-yyyy hh24:mi:ss'),

job_class           => 'DEFAULT_JOB_CLASS',

enabled             => true,

auto_drop           => false,

comments            => '费用');

end;