oracle 定时任务（scheduler job）

怎样从数据库中查询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_date ASDATE) 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)

IS

 BEGIN

   INSERTINTO wty_test_scheduler VALUES (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_date ASDATE) FROM wty_test_schedulert;

 

--dbms_sheduler运行信息

SELECT

      t.job_name,

      t.ENABLED,

      cast(t.last_start_date ASDATE),

      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_DATE ASDATE) start_date,

      CAST(t.LOG_DATE ASDATE) log_date

 FROM user_scheduler_job_run_details t

WHEREt.JOB_NAME='JOB_CREATE_WTY_TEST'

  ANDTRUNC(CAST(t.LOG_DATE ASDATE))=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_date ASDATE) 最后运行时间,

      CAST(t.next_run_date ASDATE) 下次运行时间

 FROM user_scheduler_jobs t

WHERE t.job_name='JOB_ROSANU';

 

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

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

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