[bbk5164]第57集-第6章 - 用scheduler自动化 05

1、在创建scheduler时，设置start_date =>systimestamp要比start_date => sysdate要好一些,systimestamp可以自动调整时区.

2、job创建参数中，job_type的参数值类型

　　STORED_PROCEDURE------->job执行存储过程

　　PLSQL_BLOCK------------>pl/sql匿名块

　　EXECUTABLE------------->可执行操作系统命令　　

　　The type of this job.Valid values are: 'PLSQL_BLOCK', 'STORED_PROCEDURE', 'EXECUTABLE', and 'CHAIN'.
　　If this is set, program_name must be NULL.

《 高级队列课程》

Creating a Time-Based Job

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

BEGIN
        DBMS_SCHEDULE.CREATE_JOB(
                job_name        => 'HR.DO_BACKUP',
                job_type        => 'EXECUTABLE',
                job_action      => '/home/usr/dba/rman/nightly_incr.sh',
                start_date      => SYSTIMESTAMP,
                repeat_interval => 'FREQ=DAILY;BYHOUR=23',
                                        /* next night at 11:00 PM */
                comments        => 'Nightly incremental backups'
        );
END;
/

Creating an Event-Based Schedule

To create an event-based job,you must set:

• A queue specification (where your application enqueues messages to start a job)
• An event condition (same syntax as an Oracle Streams AQ rule condition) that if TRUE starts the job.

Creating Event-Based Schedules with Enterprise Manager

Creating an Event-Based Job

Example:Create a job that runs if a batch load data file arrives on the file system before 9:00 AM.

BEGIN
        DBMS_SCHEDULER.CREATE_JOB(
                job_name        => 'ADMIN.PERFORM_DATA_LOAD',
                job_type        => 'EXECUTABLE',
                job_action      => '/loaddir/start_my_load.sh',
                start_date      => SYSTIMESTAMP,
                event_condition => 'tab.user_data.object_owner = "HR" and
                                    tab.user_data.object_name  = "DATA.TXT" and
                                    tab.user_data.event_type   = "FILE_ARRIVAL" and
                                    tab.user_data.event_timestamp < 9 ',
                queue_spec      => 'HR.LOAD_JOB_EVENT_Q'
        );
END;
/

Event-Based Scheduing

Event types:

• User- or application-generated events
• Scheduler-generated events

Events raised by Scheduler jobs:

• JOB_STARTED
• JOB_SUCCEEDED
• JOB_FAILED
• JOB_BROKEN
• JOB_COMPLETED
• JOB_STOPPED
• JOB_SCH_LIM_REACHED
• JOB_DISABLED
• JOB_CHAIN_STALLED
• JOB_ALL_EVENTS
• JOB_RUN_COMPLETED
• JOB_OVER_MAX_DUR

Example of raising an event

DBMS_SCHEDULER.SET_ATTRIBUTE('hr.do_backup','raise_events',DBMS_SCHEDULER.JOB_FAILED);