联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1.create job
BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name =>'xifenfei_job', job_type =>'STORED_PROCEDURE', job_action =>'p_schedule', repeat_interval =>'FREQ=DAILY; BYHOUR=18,20,22', enabled => true, comments => 'XIFENFEI'); END; --每天18/20/22点执行p_schedule过程
2.disable job
BEGIN
DBMS_SCHEDULER.DISABLE(name => 'xifenfei_job');
END;
3.enable job
BEGIN
DBMS_SCHEDULER.ENABLE(name => 'xifenfei_job');
END;
4.select job
select * from USER_SCHEDULER_JOBS;
5.query logs
SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE job_name='XIFENFEI_JOB';
6.delete logs
--ALL_SCHEDULER_JOB_RUN_DETAILS视图
CREATE OR REPLACE VIEW ALL_SCHEDULER_JOB_RUN_DETAILS
(log_id, log_date, owner, job_name, job_subname, status, error#,
req_start_date, actual_start_date, run_duration, instance_id,
session_id, slave_pid, cpu_used, credential_owner, credential_name,
destination_owner, destination, additional_info)
AS
(SELECT
j.LOG_ID, j.LOG_DATE, e.OWNER,
DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)),
DECODE(instr(e.NAME,'"'),0,NULL,substr(e.NAME,instr(e.NAME,'"')+1)),
e.STATUS, j.ERROR#, j.REQ_START_DATE, j.START_DATE, j.RUN_DURATION,
j.INSTANCE_ID, j.SESSION_ID, j.SLAVE_PID, j.CPU_USED,
decode(e.credential, NULL, NULL,
substr(e.credential, 1, instr(e.credential, '"')-1)),
decode(e.credential, NULL, NULL,
substr(e.credential, instr(e.credential, '"')+1,
length(e.credential) - instr(e.credential, '"'))),
decode(bitand(e.flags, 1), 0, NULL,
substr(e.destination, 1, instr(e.destination, '"')-1)),
decode(bitand(e.flags, 1), 0, e.destination,
substr(e.destination, instr(e.destination, '"')+1,
length(e.destination) - instr(e.destination, '"'))),
j.ADDITIONAL_INFO
FROM scheduler$_job_run_details j, scheduler$_event_log e
WHERE j.log_id = e.log_id
AND e.type# = 66 and e.dbid is null
AND ( e.owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
or /* user has object privileges */
( select jo.obj# from obj$ jo, user$ ju where
DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1))
= jo.name and e.owner = ju.name and jo.owner# = ju.user#
and jo.subname is null and jo.type# = 66
) in
( select oa.obj#
from sys.objauth$ oa
where grantee# in ( select kzsrorol from x$kzsro )
)
or /* user has system privileges */
(exists ( select null from v$enabledprivs
where priv_number = -265 /* CREATE ANY JOB */
)
and e.owner!='SYS')
)
);
--从这个视图中可以发现，日志有存在SCHEDULER$_JOB_RUN_DETAILS和
--SCHEDULER$_EVENT_LOG两张表中,所以要删除日志，就需要处理这两张表
######################删除日志操作#################
--删除SYS.SCHEDULER$_JOB_RUN_DETAILS中数据
DELETE FROM SYS.SCHEDULER$_JOB_RUN_DETAILS A
WHERE EXISTS (SELECT 1
FROM SYS.SCHEDULER$_EVENT_LOG B
WHERE B.NAME = 'XIFENFEI_JOB'
AND A.LOG_ID = B.LOG_ID);
--删除SYS.SCHEDULER$_EVENT_LOG中数据
DELETE FROM SYS.SCHEDULER$_EVENT_LOG B
WHERE B.NAME = 'XIFENFEI_JOB';
--提交
COMMIT;
#####################################################
7.delete jobs
BEGIN
DBMS_SCHEDULER.DROP_JOB(job_name => 'xifenfei_job');
END;
参考:http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm
常用执行频率
job_type
This attribute specifies the type of job that you are creating. If it is not specified, an error is generated. The supported values are: