oracle 定时任务（scheduler job）

来源：互联网发布：cocos2d js 图片跨域编辑：程序博客网时间：2024/06/01 23:05

怎样从数据库中查询job的属性

select * fromuser_scheduler_jobs;

或者调用DBMS_SCHEDULER包中的GET_ATTRIBUTE

--============================================================

--dbms_scheduler的使用

--============================================================

--【sql】

CREATETABLE wty_test_scheduler

(

r_id VARCHAR2(10),

r_date TIMESTAMP(6)

);

--创建job

BEGIN

dbms_scheduler.create_job(JOB_NAME=>'job_create_wty_test',

job_type=> 'PLSQL_BLOCK',

JOB_ACTION=>'BEGIN

INSERT INTO wty_test_scheduler VALUES ("JOB",SYSDATE);

COMMIT;

END;',

ENABLED=>TRUE,

start_date=>SYSTIMESTAMP,

repeat_interval=>'SYSTIMESTAMP + 1/1440',

comments=>'job_create_wty_test'

);

END;

SELECT t.r_id,CAST(t.r_dateASDATE)FROM wty_test_schedulert;

EXECdbms_scheduler.drop_job('job_create_wty_test');

SELECT *FROMuser_scheduler_jobs;

--【create job结合create_program】

CREATEORREPLACEPROCEDURE sp_wty_test_scheduler

(in_id VARCHAR2)

BEGIN

INSERTINTO wty_test_schedulerVALUES (in_id,SYSDATE);

COMMIT;

END;

--创建program

BEGIN

dbms_scheduler.create_program(program_name=>'program_wty_test_scheduler',

program_action=>'sp_wty_test_scheduler',

program_type=>'stored_procedure',

number_of_arguments=>1,

comments=>'wty_test_scheduler_program',

enabled =>FALSE

);

END;

EXECdbms_scheduler.drop_program('program_wty_test_scheduler');

SELECT *FROMuser_scheduler_programs;

--设置progam参数

BEGIN

dbms_scheduler.define_program_argument(program_name=>'program_wty_test_scheduler',

argument_position=>1,

argument_type=>'varchar2',

default_value =>'program'

);

END;

--执行program (命令窗口)

EXECdbms_scheduler.enable('program_wty_test_scheduler');

SELECT t.r_id,CAST(t.r_dateASDATE)FROM wty_test_schedulert;

--dbms_sheduler运行信息

SELECT

t.job_name,

t.ENABLED,

cast(t.last_start_dateASDATE),

t.SCHEDULE_NAME

FROM user_scheduler_jobs t

WHEREt.job_name='JOB_CREATE_WTY_TEST';

--dbms_scheduler运行成功与否信息

SELECT

t.JOB_NAME,

t.STATUS,

CAST(t.ACTUAL_START_DATEASDATE) start_date,

CAST(t.LOG_DATEASDATE) log_date

FROM user_scheduler_job_run_details t

WHEREt.JOB_NAME='JOB_CREATE_WTY_TEST'

ANDTRUNC(CAST(t.LOG_DATEASDATE))=DATE'2016-11-16'

ORDERBY4DESC;

--查询执行时间情况

SELECT

t1.WINDOW_NAME,

t1.REPEAT_INTERVAL,

t1.duration

FROM dba_scheduler_windows t1,

dba_scheduler_wingroup_memberst2

WHEREt1.WINDOW_NAME=t2.WINDOW_NAME

AND t2.WINDOW_GROUP_NAME='MAINTENANCE_WINDOW_GROUP';

--修改执行时间

BEGIN

dbms_scheduler.set_attribute('WEEKEND_WINDOW','REOEAT_INTERVAL','freq=daily;byday=SAT;byhour=0;bysecond=0');

dbms_scheduler.set_attribute('WEEKEND_WINDOW','DURATION','+000 04:00:00');

END;

--将job JOB_ROSANU变成可运行状态

BEGIN

dbms_scheduler.enable('program_wty_test_scheduler');

END;

--查job运行时长

SELECT

t.job_name,

t.STATE,

t.ENABLED,

CAST(t.last_start_dateASDATE)最后运行时间,

CAST(t.next_run_dateASDATE)下次运行时间

FROM user_scheduler_jobs t

WHERE t.job_name='JOB_ROSANU';

注意：查看定时任务执行时间根据REPEAT_INTERVAL来看如下：

FREQ=DAILY; BYHOUR=23;BYMINUTE=30;BYSECOND=0

执行时间为每天23:00:00

阅读全文

0 0