Oracle 10g Scheduler笔记

Oracle 10g 强大的scheduler功能提供了模块化方式来管理oracle环境的定时任务。优点是更容易管理，高重用。

Scheduler的基本元素：job, schedule, program, window。

1 创建job：可以创建plsql, stored_procedure,executable类型的job

1.1 plsql的job

创建测试表

CREATE TABLE TEST ( COL1 DATE);

如果job已存在，先删除这个job

BEGIN
DBMS_SCHEDULER.DROP_JOB (
   job_name           =>  'TESTJOB');
END;
/

chua

创建job，每隔5分钟执行一次。

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'TESTJOB',
   job_type           =>  'PLSQL_BLOCK',
   job_action         =>  'BEGIN INSERT INTO TEST VALUES (SYSDATE); COMMIT; END;',
   start_date         =>  SYSDATE,
   repeat_interval    =>  'FREQ=MINUTELY;INTERVAL=5',
   comments           =>  'My new job');
END;
/

激活job，新job默认是disable

BEGIN
DBMS_SCHEDULER.ENABLE (
   name           =>  'TESTJOB');
END;
/

察看job的状态

select job_name,state,enabled from user_scheduler_jobs

JOB_NAME                       STATE           ENABL
------------------------------ --------------- -----
TESTJOB                        SCHEDULED       TRUE
TESTAA                         SCHEDULED       TRUE

1.2 stored_procedure的job

创建store procedure

create or replace procedure testproc (p_date IN date)
is
begin
  insert into test values (p_date);
  commit;
end;
/

BEGIN
DBMS_SCHEDULER.DROP_JOB (
   job_name           =>  'TESTJOB');
END;
/

创建job，参数是1，

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'TESTJOB',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'TESTPROC',
   number_of_arguments=>  1,
   start_date         =>  SYSDATE,
   repeat_interval    =>  'FREQ=MINUTELY;INTERVAL=5',
   comments           =>  'My new job');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => 'TESTJOB',
   argument_position       => 1,
   argument_value          => SYSDATE);
END;
/

激活job

BEGIN
DBMS_SCHEDULER.ENABLE (
   name           =>  'TESTJOB');
END;
/

察看job

SQL> select job_name, state, enabled from user_scheduler_jobs;

JOB_NAME                       STATE           ENABL
------------------------------ --------------- -----
TESTJOB                        SCHEDULED       TRUE
TESTAA                         SCHEDULED       TRUE

1.3 创建executable job

BEGIN
DBMS_SCHEDULER.DROP_JOB (
   job_name           =>  'TESTJOB');
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'TESTJOB',
   job_type           =>  'EXECUTABLE',
   job_action         =>  '/bin/date',
   start_date         =>  to_date('2010-08-29 18:00:00','YYYY-MM-DD HH24:MI:SS'),
   repeat_interval    =>  'FREQ=MINUTELY;INTERVAL=5',
   end_date           =>  to_date('2010-09-29 18:00:00','YYYY-MM-DD HH24:MI:SS'),
   comments           =>  'My new job');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE (
   name           =>  'TESTJOB');
END;
/

2. 创建schedule，schedule表示job在何时运行，并运行几次。

BEGIN
DBMS_SCHEDULER.DROP_SCHEDULE (
  schedule_name     => 'my_stats_schedule');
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
  schedule_name     => 'my_stats_schedule',
  start_date        => SYSTIMESTAMP,
  end_date          => SYSTIMESTAMP + INTERVAL '30' day,
  repeat_interval   => 'FREQ=MINUTELY; INTERVAL=5',
  comments          => 'Every 5 minutes');
END;
/

BEGIN
DBMS_SCHEDULER.DROP_JOB (
   job_name           =>  'TESTJOB');
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'TESTJOB',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'TESTPROC',
   number_of_arguments=>  1,
   schedule_name      =>  'my_stats_schedule',
   comments           =>  'My new job');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => 'TESTJOB',
   argument_position       => 1,
   argument_value          => SYSDATE);
END;
/

3.创建window，window可以为job在指定时间范围内使用指定的resource

BEGIN
DBMS_SCHEDULER.DROP_WINDOW (
   window_name          =>  'my_window1');
END;
/

这里resource plan取默认值，

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW (
   window_name          =>  'my_window1',
   resource_plan        =>  null,
   start_date           =>  to_date('2010-08-29 13:00:00','YYYY-MM-DD HH24:MI:SS'),
   repeat_interval      =>  'FREQ=MINUTELY;INTERVAL=5',
   end_date             =>  to_date('2010-09-29 18:00:00','YYYY-MM-DD HH24:MI:SS'),
   duration             =>  interval '80' MINUTE,
   comments             =>  'This is my first window');
END;
/

将window分配到job

BEGIN
DBMS_SCHEDULER.DROP_JOB (
   job_name           =>  'TESTJOB');
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'TESTJOB',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'TESTPROC',
   number_of_arguments=>  1,
   schedule_name      =>  'my_window1',
   comments           =>  'My new job');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => 'TESTJOB',
   argument_position       => 1,
   argument_value          => SYSDATE);
END;
/

4.创建program，program对象定义了程序类型和做什么，这样program可以被重用。

BEGIN
DBMS_SCHEDULER.DROP_PROGRAM (
   program_name           => 'aaa.test_program');
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name          => 'test_program',
   program_type          => 'STORED_PROCEDURE',
   program_action        => 'testproc',
   number_of_arguments   => 1,
   enabled               => FALSE,
   comments              => 'My comments here');
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
   program_name            => 'test_program',
   argument_position       => 1,
   argument_name           => 'p_date',
   argument_type           => 'DATE',
   default_value           => SYSDATE);
dbms_scheduler.enable ('test_program');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE (
   name           => 'aaa.test_os_program');
END;
/

将program用于job

BEGIN
DBMS_SCHEDULER.DROP_JOB (
   job_name           =>  'TESTJOB');
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'TESTJOB',
   program_name       =>  'test_program',
   schedule_name      =>  'my_stats_schedule',
   comments           =>  'My new job');
END;
/

5. 查看job运行日志

col job_name format a30
SELECT JOB_NAME, OPERATION, OWNER, to_char(log_date,'yyyy-mm-dd hh24:mi:ss') FROM USER_SCHEDULER_JOB_LOG
where job_name = 'TESTJOB'
order by log_date;

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27143/viewspace-672076/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27143/viewspace-672076/