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

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;

ITPub博客

ORACLE查询JOB信息及JOB创建

最新文章