Use of DBMS_SCHEDULER

Use of DBMS_SCHEDULER 1. Use DBMS_SCHEDULER.CREATE_JOB to directly CREATE a job [SQL] CREATE TABLE T_ROSANU (R_ID VARCHAR2 (10), R_DATE TIMESTAMP (6); -- the TABLE has been created. BEGIN DBMS_SCHEDULER.CREATE_JOB (JOB_NAME => 'job _ create_rosanu ', JOB_TYPE => 'plsql _ Block', JOB_ACTION => 'in in insert into T_ROSANU VALUES ('job', SYSDATE ); COMMIT; END; ', ENABLED => TRUE, START_DATE => paiimestamp, REPEAT_INTERVAL => 'reschedimestamp + 100', COMMENTS => 'rosanu _ create_job'); END; -- the PL/SQL process has been completed successfully. SELECT R_ID, TO_CHAR (R_DATE, 'yyyy-mm-dd hh24: mi: ss') FROM T_ROSANU;/* R_ID TO_CHAR (R_DATE, 'yyyy-mm-dd hh24: mi: ss) ------------------------------------------- job 2013-03-29 18:12:11 job 2013-03-29 18:13:11 job 2013-03-29 18:14:11 */The usage here is similar to that of dbms_jobs, but this provides a flexible usage method, for example, anonymous blocks and operating system commands can be executed. 2. CREATE_JOB combined with CREATE_PROGRAM [SQL] CREATE OR REPLACE PROCEDURE PROC_ROSANU (IN_ID IN VARCHAR2) IS BEGIN IN Sert into T_ROSANU VALUES (IN_ID, SYSDATE); COMMIT; END; -- the process has been created. -- Create Program BEGIN tables (PROGRAM_NAME => 'program _ rosanu ', PROGRAM_ACTION => 'proc _ ROSANU', PROGRAM_TYPE => 'stored _ PROCEDURE ', NUMBER_OF_ARGUMENTS => 1, COMMENTS => 'rosanu _ PROGRAM ', ENABLED => FALSE); END; -- the PL/SQL process has been completed successfully. -- Set the Program parameter BEGIN (PROGRAM_NAME => 'program _ rosanu ', ARGUMENT_POSITION => 1, ARGUMENT_TYPE => 'varchar2', DEFAULT_VALUE => 'program'); END; -- the PL/SQL process has been completed successfully. -- Execute Program EXEC DBMS_SCHEDULER.ENABLE ('program _ rosanu '); -- the PL/SQL process has been completed successfully. -- Create Job BEGIN DBMS_SCHEDULER.CREATE_JOB (JOB_NAME => 'job _ rosanu ', PROGRAM_NAME => 'program _ rosanu', COMMENTS => 'rosanu _ create_job ', REPEAT_INTERVAL => 'resumimestamp + 100', AUTO_DROP => FALSE, ENABLED => TRUE); END; -- PL/SQL process completed successfully. SELECT R_ID, TO_CHAR (R_DATE, 'yyyy-mm-dd hh24: mi: ss') FROM T_ROSANU;/* R_ID TO_CHAR (R_DATE, 'yyyy-mm-dd hh24: mi: ss ') Explain job 20:20:11 program 2013-02-29 20:20:09 program 2013-02-29 20:21:09 job 2013-02-29 20:21:11 */The parameters used here can be seen that CREATE_PROGRAM gives some parameters in CREATE_JOB to independent, make the control more flexible; 3. CREATE_JOB combines CREATE_PROGRAM and CREATE_SCHEDULE [SQL] exec DBMS_SCHEDULE R. drop_job ('job _ create_rosanu '); -- the PL/SQL process has been completed successfully. Truncate table t_rosanu; -- the table is truncated. BEGIN aggregate (REPEAT_INTERVAL => 'freq = MINUTELY; INTERVAL = 1', START_DATE => SYSDATE, COMMENTS => 'rosanu _ create_job ', SCHEDULE_NAME => 'schedule _ rosanu '); END; -- PL/SQL process completed successfully. BEGIN aggregate (JOB_NAME => 't_ xifenfei_job ', PROGRAM_NAME => 'program _ rosanu', COMMENTS => 'rosanu _ create_job ', SCHEDULE_NAME => 'schedule _ rosanu ', AUTO_DROP => FALSE, ENABLED => TRUE); END; -- PL/SQL process completed successfully. SELECT R_ID, TO_CHAR (R_DATE, 'yyyy-mm-dd hh24: mi: ss') FROM T_ROSANU;/* R_ID TO_CHAR (R_DATE, 'yyyy-mm-dd hh24: mi: ss ') ---------- explain job 2013-03-29 21:39:11 job 2013-03-29 21:37:11 job 2013-03-29 21:38:11 program 2013-03-29 21:39:01 program 2013-03-29 21:40:01 */from here we can see that CREATE_SCHEDULE separates the execution plan from CREATE_JOB, make the control more powerful and more flexible. Additional instructions: 1. you can also create JOB_CLASS to control resource usage more flexibly. You must modify resource_consumer_group in JOB_CLASS to control the resource. The service corresponds to the service in the database, and the node in rac can be executed; 2. use DBMS_SCHEDULER.set_attribute to modify the relevant attributes, for example, [SQL] EXEC DBMS_SCHEDULER.set_attribute ('gather _ STATS_JOB ', 'job _ class', 'Auto _ TASKS_JOB_CLASS2 '); EXEC dbms_scheduler.set_attribute ('weeknight _ Window', 'Repeat _ INTERVAL ', 'freq = daily; byday = MON, TUE, WED, THU, FRI; byhour = 2; byminute = 0; bysecond = 0'); [SQL] -- DBMS_SCHEDULER run Information SELECT JOB_NAME, STATE, ENABLED, TO_CHAR (LAST_START_DATE, 'yyyy-mm-dd hh24: mi: ss '), SCHEDULE_NAME FROM success; -- SELECT LOG_ID, JOB_NAME, STATUS, TO_CHAR (ACTUAL_START_DATE, 'yyyy-mm-dd HH24: MI: ss') START_DATE, TO_CHAR (LOG_DATE, 'yyyy-mm-dd HH24: MI: ss') LOG_DATE FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'job _ ROSANU 'order by 4 DESC; -- query execution time SELECT T1.WINDOW _ NAME, t1.REPEAT _ INTERVAL, T1.DURATION FROM DBA_SCHEDULER_WINDOWS T1, DBA_SCHEDULER_WINGROUP_MEMBERS T2 WHERE T1.WINDOW _ NAME = T2.WINDOW _ name and T2.WINDOW _ GROUP_NAME = 'Maintenance _ WINDOW_GROUP '; -- modify the execution time BEGINDBMS_SCHEDULER.SET_ATTRIBUTE ('weekend _ Window', 'Repeat _ INTERVAL ', 'freq = daily; byday = SAT; byhour = 0; byminute = 0; bysecond = 0'); DBMS_SCHEDULER.SET_ATTRIBUTE ('weekend _ Window', 'duration', '+ 002 00:00:00'); END; [SQL] -- change Job JOB_ROSANU TO THE runable state BEGIN DBMS_SCHEDULER.ENABLE ('job _ ROSANU '); END; -- Query JOB run time SELECT T. JOB_NAME, T. STATE, T. ENABLED, TO_CHAR (T. LAST_START_DATE, 'yyyy-mm-dd hh24: mi: ss') AS last run time, TO_CHAR (T. NEXT_RUN_DATE, 'yyyy-mm-dd hh24: mi: ss') AS next run time, T. SCHEDULE_NAME FROM DBA_SCHEDULER_JOBS t where t. JOB_NAME = 'job _ ROSANU ';