1. 简介
   在Oracle 10g之前，我们通过DBMS_JOB来管理定时任务；
   而10g之后，则推荐使用DBMS_SCHEDULER来管理定时任务，因为它提供了更强大的功能和灵活的机制。
2. 需要的权限
   CREATE JOB – (必须, 要执行DBMS_SCHEDULER, 需要有create job权限)
   CREATE EXTERNAL JOB – (可选, 创建执行操作系统命令的job时需要)

查询用户所拥有的角色以及角色所包含的权限

select * from role_sys_privs where role in (
select granted_role from dba_role_privs where grantee=‘SCOTT’
) order by role;

查询直接授予用户的权限

select * from dba_sys_privs where grantee=‘SCOTT’;
3. 一个简单的Demo
3.1 创建JOB
create table test_t1(id int, create_date date);
create or replace procedure test_p1
is
v_maxId test_t1.id%type := 1;
begin
select nvl(max(id), 0) into v_maxId from test_t1;
insert into test_t1 values(v_maxId + 1, sysdate);
commit;
end test_p1;
/
declare
v_count int := 0;
begin
select count(*) into v_count from user_scheduler_jobs where job_name=‘TEST_JOB1’;
if v_count > 0 then
dbms_scheduler.drop_job(‘TEST_JOB1’);
end if;
dbms_scheduler.create_job (
job_name => ‘test_job1’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘TEST_P1’,
start_date => sysdate,
repeat_interval => ‘FREQ=MINUTELY;INTERVAL=1’,
enabled => true
);
end;
/
参数说明
job_name : 必选, 任务名称
job_type : 必选, 任务类型(
PLSQL_BLOCK, – 执行一个PL/SQL匿名快
STORED_PROCEDURE, – 执行一个存储过程
EXECUTABLE, – 执行一个外部程序
CHAIN – 执行一个CHAIN
)
job_action : 必选, 任务内容, 与job_type配合使用
start_date : 可选, 首次执行时间, 为空时表示立即执行
repeat_interval : 可选, 执行频率, 为空时表示只执行一次(
FREQ=MINUTELY; – 表示间隔单位, 可选值有YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, SECONDLY
INTERVAL=1 – 表示间隔周期
)
enabled : 可选, 是否启用任务
详细参数可参考: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72960
3.2 查看JOB执行情况
– 查看已创建的JOB
select job_name, job_type, enabled, state from user_scheduler_jobs;
– 查看JOB运行日志
select log_id, log_date, status from user_scheduler_job_run_details where job_name=‘TEST_JOB1’;
3.3 手工运行job
dbms_scheduler.run_job(job_name => ‘TEST_JOB1’);
3.4 手工停止job
dbms_scheduler.stop_job(job_name => 'TEST_JOB1’);
3.5 手工复制job
exec dbms_scheduler.copy_job(old_job =>‘TEST_JOB1’ ,new_job =>‘TEST_JOB2’ );
3.6 手工删除job
exec dbms_scheduler.drop_job(job_name => ‘TEST_JOB1’);
3.7 激活job
call dbms_scheduler.enable(name =>‘TEST_JOB1’);
3.8 禁用job
call dbms_scheduler.disable(name =>‘TEST_JOB1’);

dbms_scheduler带参数的job创建

1.创建测试表
CREATE TABLE t_test
(
ID DATE PRIMARY KEY ,
VALUE NUMBER
);

2.创建存储过程
create or replace procedure pro_test(v IN NUMBER )
is
begin
insert into t_test(id,VALUE) values (sysdate,v);
end ;
/

3.创建JOB
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME => ’ HUYVANPULL_TEST_JOB1 ’ ,
JOB_TYPE => ’ STORED_PROCEDURE ’ ,
JOB_ACTION => ’ PRO_TEST ’ ,
NUMBER_OF_ARGUMENTS => 1 ,
REPEAT_INTERVAL => ’ FREQ=MINUTELY;INTERVAL=1 ’ ,
ENABLED => FALSE,
START_DATE => SYSDATE,
COMMENTS => ’ HUYVANPULL-Test ’ );
END ;

如果没有创建权限,赋权限
Grant Create Job To em_test;

注意：使用ARGUMENT_POSITION时，创建JOB的时候一定要指定NUMBER_OF_ARGUMENTS参数
使用 ARGUMENT_NAME时，需要使用PROGRAM

4.设置存储JOB的存储过程的参数
BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
JOB_NAME =>’ HUYVANPULL_TEST_JOB1 ’ ,
ARGUMENT_ =>’ V’ ,
ARGUMENT_VALUE => 0
);
END ;

5.启动JOB
BEGIN
DBMS_SCHEDULER.enable( ’ HUYVANPULL_TEST_JOB1 ’ );
END ;

6.查询JOB
SELECT * FROM USER_SCHEDULER_JOBS;

7.停用JOB
BEGIN
DBMS_SCHEDULER.disable( ’ HUYVANPULL_TEST_JOB1 ’ );
END ;

8.删除JOB
BEGIN
DBMS_SCHEDULER.drop_job( ’ HUYVANPULL_TEST_JOB1 ’ );
END ;
清除job运行日志的方法

修改Oracle的Job Scheduler 日志级别及删除运行日志

Job 运行久了会产生大量运行日志，这些信息可通过下面的方式清除:

1、创建job：

Create job links to previous job class.

DBMS_SCHEDULER.create_job (

job_name => ‘test_log_job’,

job_type => ‘PLSQL_BLOCK’,

job_action => ‘BEGIN NULL; END;’,

job_class => ‘no_logging_class’,

enabled => FALSE,

auto_drop => FALSE,

comments => ‘Job used to job logs.’);

2、job的日志级别：

–job日志级别由两个因素决定,一个是job自己日志级别,别一个是使用的job cloass的日志级别,取两者中的最高值。

–job创建时日志级别默认是DBMS_SCHEDULER.LOGGING_RUNS,

–创建的job时,如果不指定job class,默认为DEFAULT_JOB_CLASS,而DEFAULT_JOB_CLASS默认logging level是DBMS_SCHEDULER.LOGGING_RUNS,

–所以创建的job的logging level至少是LOGGING_RUNS。

–如果不产生日志,必须禁止job自己日志级别,同时不能使用默认的默认为DEFAULT_JOB_CLASS.

– 重新建立无日志job class：

begin

DBMS_SCHEDULER.create_job_class (

job_class_name => ‘no_logging_class’,

resource_consumer_group => ‘default_consumer_group’,

logging_level => DBMS_SCHEDULER.LOGGING_OFF);

end;

–禁止job自身日志,修改日志级别LOGGING_LEVEL属性：

BEGIN

dbms_scheduler.set_attribute(‘JOB_NAME’,‘LOGGING_LEVEL’,DBMS_SCHEDULER.LOGGING_OFF);

END;

–1)DBMS_SCHEDULER.LOGGING_OFF：关闭日志记录功能；

–2)DBMS_SCHEDULER.LOGGING_RUNS：对任务的运行信息进行记录；

–3)DBMS_SCHEDULER.LOGGING_FULL：记录任务所有相关信息，不仅有任务的运行情况，甚至连任务的创建、修改等也均将记入日志。

3、查询和删除Job Log：

–在当前普通用户和SYS用户中都可以查看SCHEDULER_JOBS生成的日志(名称全用大写)

select * from all_scheduler_job_log where owner=‘USERNAME’ and job_name=‘XXX’

select * from all_scheduler_job_run_details where owner=‘USERNAME’ and job_name='XXX’and job_name=‘XXX’

–以SYS用户登录，删除某用户下的某个job的运行信息：

delete from all_scheduler_job_run_details where owner=‘USERNAME’ and job_name=‘XXX’

执行以上命令，报如下错误
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table
明显all_scheduler_job_run_details是个视图，需要找到基表

CREATEORREPLACEVIEW ALL_SCHEDULER_JOB_LOG
(log_id, log_date, owner, job_name, job_subname, job_class, operation, status, user_name, client_id, global_uid, credential_owner, credential_name, destination_owner, destination, additional_info)
AS
(SELECT
e.LOG_ID, e.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)),
co.NAME, OPERATION, e.STATUS, e.USER_NAME, e.CLIENT_ID, e.GUID,
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, ‘"’))),
e.ADDITIONAL_INFO
FROMscheduler$_event_log e, obj$ co
WHERE e.type# = 66and e.dbid isnulland e.class_id = co.obj#(+)
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 isnull
) in
( select oa.obj#
from sys.objauth$ oa
where grantee# in ( select kzsrorol from x$kzsro ) ) or/* user has system privileges */ (exists ( selectnullfrom v$enabledprivs
where priv_number = -265/ CREATE ANY JOB */
)
and e.owner!=‘SYS’)
)
);
commentontable ALL_SCHEDULER_JOB_LOG is’Logged information for all scheduler jobs’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.LOG_ID is’The unique id that identifies a row’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.LOG_DATE is’The date of this log entry’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.OWNER is’The owner of the scheduler job’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.JOB_NAME is’The name of the scheduler job’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.JOB_SUBNAME is’The subname of the scheduler job (for a chain step job)’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.JOB_CLASS is’The class the job belonged to at the time of entry’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.OPERATION is’The operation corresponding to this log entry’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.STATUS is’The status of the operation, if applicable’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.USER_NAME is’The name of the user who performed the operation, if applicable’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.CLIENT_ID is’The client id of the user who performed the operation, if applicable’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.GLOBAL_UID is’The global_uid of the user who performed the operation, if applicable’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.CREDENTIAL_OWNER is’Owner of the credential used for this remote job run’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.CREDENTIAL_NAME is’Name of the credential used for this remote job run’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.DESTINATION_OWNER is’Owner of destination object used in remote run or NULL if no object used’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.DESTINATION is’The destination for a remote job operation’;
commentoncolumn ALL_SCHEDULER_JOB_LOG.ADDITIONAL_INFO is’Additional information on this entry, if applicable’;

因此，scheduler$_event_log是基表，在基表中删除相关数据即可。

deletefrom scheduler$_event_log
where owner='TEST’andname=‘TEST_JOB1’;