oracle 定時任務（scheduler job）____oracle

怎樣從資料庫中查詢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