[bbk5157]第54集-第6章 - 用scheduler自动化 02

Your Basic Work Flow

To simplify management tasks with the Scheduler:

1. Create a program (enabled or disabled)---optional
   1. To reuse this action within multiple jobs
   2. To change the schedule for a job without having to re-create the PL/SQL block
2. Create and use a schedule.
3. Create and submit a job.

Calendaring Expressions

创建job步骤

前提条件:

　　1、当前用户需要具备权限create job or create any job

　　2、创建一个存储过程，供program调用　　　　

create or replace procedure add_dept
is
v_dept_id HR.dept.department_id%TYPE;
v_dept_name HR.dept.department_name%TYPE;
BEGIN
   v_dept_id :=280;
   v_dept_name := 'ST-Curriculum';
   INSERT INTO dept(department_id,department_name) VALUES(v_dept_id,v_dept_name);
   COMMIT;
END;
/

steps：

　　1、Creating a Program

BEGIN
        DBMS_SCHEDULER.CREATE_PROGRAM(
                program_name => 'program_add_dept',
                program_action => 'U2.ADD_DEPT',
                program_type => 'STORED_PROCEDURE',
                enabled => TRUE
        );
END;
/

　　2、Creating and Using Schedules

BEGIN
        DBMS_SCHEDULER.CREATE_SCHEDULE(
                schedule_name => 'add_dept_schedule',
                start_date => SYSTIMESTAMP,
                end_date => SYSTIMESTAMP + 10,
                repeat_interval => 'FREQ=HOURLY;INTERVAL=1',
                comments => 'Every hour'
        );
END;
/

　　3、creating and Runing a Job

BEGIN
        DBMS_SCHEDULER.CREATE_JOB(
                job_name => 'U2.add_dept_job',
                program_name => 'U2.program_add_dept',
                schedule_name => 'add_dept_schedule'
        );
END;
/
~

　　4、Monitoring a Job

SQL> SELECT job_name,status,error#,run_duration FROM USER_SCHEDULER_JOB_RUN_DETAILS;

no rows selected

Creating a Time-Based Job

Example:Create a job that calls a backup script every night at 11:00,starting tonight.

        DBMS_SCHEDULER.CREATE_JOB(
                job_name => 'HR.DO_BACKUP',
                job_type => 'EXECUTABLE',
                job_action => '/home/usr/dba/rman/nightly_incr.sh',
                start_date => SYSDATE,
                repeat_interval => 'freq=daily;byhour=23',
                                /* next night at 11:pm */
                comments => 'Nightly incremental backups'
        );
END;
/