/*
|| Oracle 10g Scheduler - Listing 3
||
|| Contains:
||
|| - Various DBMS_SCHEDULER examples
||
|| for demonstration of Oracle 10g Scheduler advanced features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g
|| Scheduler and should be carefully proofread before executing it against
|| any existing Oracle database to insure that no potential damage can occur.
||
*/
 
-----
-- Listing 3.1: Creating Scheduler Job Classes
-----
BEGIN
    DBMS_SCHEDULER.CREATE_JOB_CLASS(
         job_class_name => 'AccountingRpts'
        ,resource_consumer_group => NULL
        ,service => NULL
        ,logging_level => DBMS_SCHEDULER.LOGGING_FULL
        ,log_history => 60
        ,comments => 'Jobs responsible for Accounting Reports'
    );
    DBMS_SCHEDULER.CREATE_JOB_CLASS(
         job_class_name => 'DBManagement'
        ,resource_consumer_group => NULL
        ,service => NULL
        ,logging_level => DBMS_SCHEDULER.LOGGING_RUNS
        ,log_history => 90
        ,comments => 'Database Management jobs'
    );
END;
/
 
-----
-- Listing 3.2: Adding existing Jobs to Job Classes
-----
BEGIN
    -- Add existing Schema Refresh tasks to DBManagement Job Class
    DBMS_SCHEDULER.SET_ATTRIBUTE(
         name => 'HR_FRESHENSCHEMA'
        ,attribute => 'job_class'
        ,value => 'DBManagement'
    );
    DBMS_SCHEDULER.SET_ATTRIBUTE(
         name => 'SH_FRESHENSCHEMA'
        ,attribute => 'job_class'
        ,value => 'DBManagement'
    );
    DBMS_SCHEDULER.SET_ATTRIBUTE(
         name => 'SLSMGR_FRESHENSCHEMA'
        ,attribute => 'job_class'
        ,value => 'DBManagement'
    );
END;
/
 
-----
-- Listing 3.3: Creating Scheduler Windows
-----
BEGIN
    -- Create new Scheduler Windows
    DBMS_SCHEDULER.CREATE_WINDOW(
         window_name => 'PEAK_AM'
        ,resource_plan => 'PEAKTIME'
        ,start_date => '10/04/2004 06:00:00'
        ,duration => INTERVAL '6' HOUR
        ,repeat_interval => 'FREQ=DAILY;BYHOUR=6;BYMINUTE=0;BYDAY=MON,TUE,WED,THU,FRI'
        ,window_priority => 'HIGH'
        ,comments => 'Weekday Morning Processing'
    );
    DBMS_SCHEDULER.CREATE_WINDOW(
         window_name => 'PEAK_PM'
        ,resource_plan => 'PEAKTIME'
        ,start_date => '10/04/2004 12:00:00'
        ,duration => INTERVAL '6' HOUR
        ,repeat_interval => 'FREQ=DAILY;BYHOUR=12;BYMINUTE=0;BYDAY=MON,TUE,WED,THU,FRI'
        ,window_priority => 'HIGH'
        ,comments => 'Weekday Afternoon Processing'
    );
    DBMS_SCHEDULER.CREATE_WINDOW(
         window_name => 'OFF_DAYS'
        ,resource_plan => 'OFFPEAK'
        ,start_date => '10/04/2004 17:30:00'
        ,duration => INTERVAL '12' HOUR
        ,repeat_interval => 'FREQ=DAILY;BYHOUR=17;BYMINUTE=30;BYDAY=MON,TUE,WED,THU,FRI'
        ,window_priority => 'LOW'
        ,comments => 'Weekday Off-Peak Processing'
    );
    DBMS_SCHEDULER.CREATE_WINDOW(
         window_name => 'WKENDS'
        ,resource_plan => 'OFFPEAK'
        ,start_date => '10/09/2004 06:00:00'
        ,duration => INTERVAL '24' HOUR
        ,repeat_interval => 'FREQ=DAILY;BYDAY=SAT,SUN'
        ,window_priority => 'LOW'
        ,comments => 'Off-Peak Weekends'
    );
END;
/
 
-----
-- Listing 3.4: Creating Scheduler Windows Using a Schedule
-----
BEGIN
    DBMS_SCHEDULER.CREATE_WINDOW(
         window_name => 'SPECIALCASE'
        ,schedule_name => 'FRESHENSCHEMAS'
        ,resource_plan => 'BATCH'
        ,duration => INTERVAL '1' WEEK
        ,comments => 'Special window for emergency schema refreshes'
    );
END;
/
 
-----
-- Listing 3.5: Forcing Scheduler Windows to open and close
-----
-- Close a currently-open Window ...
BEGIN
    DBMS_SCHEDULER.CLOSE_WINDOW(
         window_name => 'OFF_DAYS'
    );
END;
/
-- ... and force a currently-closed Window to open
BEGIN
    DBMS_SCHEDULER.OPEN_WINDOW(
         window_name => 'PEAK_PM'
        ,force -> TRUE
    );
END;
/
 
-----
-- Listing 3.6: What Scheduler Windows have opened or closed recently?
-----
TTITLE 'Recent Window Status:'
COL oper_date           FORMAT A20  HEADING 'Log Date'
COL window_name         FORMAT A20  HEADING 'Window Name'
COL operation           FORMAT A12  HEADING 'Open/|Closed'

SELECT
     TO_CHAR(log_date, 'mm-dd-yyyy hh24:mi:ss') oper_date
    ,window_name
    ,operation
  FROM dba_scheduler_window_log
 WHERE operation IN ('OPEN', 'CLOSE')
 ORDER BY log_date DESC
;
 
-----
-- Listing 3.7: Creating Scheduler Window Groups
-----
BEGIN
    -- Create new Scheduler Window Groups
    DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
         group_name => 'PEAKTIME'
        ,window_list => 'PEAK_AM,PEAK_PM'
        ,comments => 'PeakTime Processing'
    );
    DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
         group_name => 'OFFPEAK'
        ,window_list => 'OFF_DAYS,WKENDS'
        ,comments => 'PeakTime Processing'
    );
END;
/
 
-----
-- Listing 3.8: Using DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING
-----
CREATE OR REPLACE PROCEDURE SYS.show_calendar_events(
     InitialDate    IN DATE
    ,ScheduleString IN VARCHAR2
    ,Iterations     IN NUMBER)
AS
    -- Local variables
    dtz_begin       TIMESTAMP;
    dtz_next        TIMESTAMP;
    dtz_returned    TIMESTAMP;

BEGIN
    -- Convert incoming date to TIMESTAMP format
    dtz_begin := TO_TIMESTAMP_TZ(InitialDate, 'mm/dd/yyyy hh24:mi:ss');
    -- Set the returned date to the start date (for 1st iteration)
    dtz_returned := dtz_begin;
    FOR idx IN 1..Iterations
        LOOP
            -- Evaluate the calendar string
            DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
                 ScheduleString
                ,dtz_begin
                ,dtz_returned
                ,dtz_next
            );
            -- Print the output
            DBMS_OUTPUT.PUT_LINE(
                'Iteration #' || TO_CHAR(idx, '99999') ||
                ' will run on  ' || dtz_next
             );
            -- Set up the next iteration
            dtz_returned := dtz_next;
        END LOOP;
END;
 
-----
-- Listing 3.9: Verifying a Scheduler Calendar String:
-- Starting on December 1, 2004, show the first 20 iterations of a scheduled
-- task that occur only on weekdays at 06:15 on those days
-----
SET SERVEROUTPUT ON
EXEC DBMS_OUTPUT.ENABLE(1000000);

BEGIN
    SYS.show_calendar_events(
         InitialDate => '12/01/2004'
        ,ScheduleString => 'FREQ=DAILY;BYHOUR=6;BYMINUTE=15;BYDAY=MON,TUE,WED,THU,FRI'
        ,Iterations => 20
    );
END;
/